When AI suggests adding an index to a slow query, I ask it to check which indexes already exist. In one device search we investigated, the trigram indexes were already there. The query was still scanning the whole table. Before adding another one, I wanted to know why the existing ones weren't helping.

Looking into it with AI, we found that Django 4.2's icontains wrapped the column in UPPER(). The trigram index was on the text column itself, but the query converted the column to uppercase first. Those expressions didn't match. Here are just the search conditions we changed:

WHERE UPPER(device_name::text) LIKE UPPER('%term%')

WHERE device_name ILIKE '%term%'

Whether that change works for your query depends on the column type, index definition, and search behavior you need.

The original test used 140,000 synthetic rows based on the real table structure, running locally on PostgreSQL 13.23. The recorded three-column search went from 111 ms to 0.34 ms and used the existing trigram indexes. A big difference, but it didn't tell us how much faster the production API would be.

I couldn't find the complete execution plans from that test. For this article, I made a separate local example with PostgreSQL 13.23 and 140,000 synthetic rows. Both query versions returned the same 32 records. These excerpts are from the new example, separate from the historical measurements above.

Before:

Gather Merge  (cost=5215.32..5224.41 rows=79 width=4) (actual time=384.911..395.064 rows=32 loops=1)
...
  Buffers: shared hit=2211
...
        ->  Parallel Seq Scan on demo_devices  (cost=0.00..4212.82 rows=79 width=4) (actual time=11.875..380.974 rows=16 loops=2)

After:

Sort  (cost=292.62..292.72 rows=42 width=4) (actual time=1.246..1.251 rows=32 loops=1)
...
  Buffers: shared hit=70
  ->  Bitmap Heap Scan on demo_devices  (cost=140.35..291.49 rows=42 width=4) (actual time=0.457..1.083 rows=32 loops=1)
...
        Heap Blocks: exact=32
...
        ->  BitmapOr  (cost=140.35..140.35 rows=42 width=0) (actual time=0.413..0.415 rows=0 loops=1)

I start with Parallel Seq Scan: the old query split the work of scanning the whole table and checked each record against the search condition. The new plan uses Bitmap Heap Scan. It finds candidate locations through the indexes, then reads the corresponding table blocks and checks the records. BitmapOr combines the locations found by the three indexes, because a match in any of the three fields counts.

Then I look at how much it read. Heap Blocks: exact=32 means this step visited 32 table blocks, not 32 records. The result also happened to contain 32 records; those are separate numbers. The top-level shared hit dropped from 2211 to 70, meaning far fewer page accesses were served from PostgreSQL's shared buffer cache. That is not a count of disk reads.

Both queries found the same 32 records. The new one touched much less data and took less time. I ask AI to explain those differences; two timings alone aren't enough. Indexes helped with this small result set. If most of the table matched, a table scan might be the better choice.

The original change also ran into another problem. One platform stored IMEI values as text[], an array of strings. The service's model hadn't kept up. Searching it as a regular text column caused an error.

So when AI says to switch to ILIKE, I ask: “Did you check the type in the actual database, or only the model?” If it has access to a safe test environment, I ask it to check. Otherwise, I supply the actual SQL, column types, and index definitions. If something is missing, say so. Don't guess from the model.

That field was later fixed with EXISTS and unnest() to match each array element. Search worked again. That query still wasn't using an index; index optimization was left for separate work. I ask AI to list what it fixed and what remains.

I also ask: “Could this change which records we find?” A separate agent review found that our custom lookup could interpret % and _ as wildcards when the search value came from an expression. A search meant to find a literal character could match more records. Some Unicode case comparisons differed too. Changing the SQL wasn't as straightforward as it first looked.

I ask it to run ordinary inputs and likely trouble cases against both versions in an isolated test database, then compare the records. Don't just tell me both returned ten rows. Are they the same ten rows? Check ordering and pagination too. If we're deliberately changing the search rules, explain which results should change.

Another question: “Did you measure the SQL or the whole API?” A faster query doesn't necessarily make the API faster. The parameters, data, and environment also need to match for the comparison to mean anything.

When AI says the change is ready, I ask for three things:

  • Before-and-after SQL: the parameters, column types, and existing indexes.
  • Before-and-after results: compare the actual records, not just counts. Include searches containing % and _, ordering, and pagination. Explain intentional differences.
  • Actual execution plans and timings: where it ran, how much data it used, how many runs, and what changed in the scans, reads, and time. If it wasn't run, say so.

Once I've seen those, I can decide whether to change the query, add an index, or look somewhere else.