Using AI to investigate an unfamiliar error or stack trace
A PostgreSQL search slowdown shows how to use AI to connect monitoring data, application code, generated SQL, and query plans during an investigation.
Table of Contents
We had recently migrated our database from MySQL to PostgreSQL and were benchmarking the new database’s performance. During this exercise, we found that product searches were taking several seconds despite having trigram indexes in place. The code looked straightforward. The indexes existed. However, the generated SQL was asking PostgreSQL to search a different expression from the one we had indexed.
To debug this I sought the help of my trusted claude and asked it to analyse different parts of my stack. Let us walk through that investigation and what it tells us about using AI when the behaviour of a system is unfamiliar.
An unfamiliar error becomes easier to investigate when we can connect it to something concrete: the request that failed, the code it executed, and the work the underlying system performed.
Start with what the system is doing
Our application stores products belonging to different companies. During the investigation, we recorded the following comparison at approximately 4,908 requests per minute, using the same db.r6i.large instance class:
| Metric | MySQL comparison | PostgreSQL |
|---|---|---|
| Database CPU utilisation | 42.7% | 94.8% |
| Average database time per request | 72 ms | 444 ms |
| Product-list database time during the peak window | 112 ms | 4.67 s |
These numbers established the scale of the problem. They did not establish why it was happening. Similar throughput and instance size still leave differences in query mix, configuration, and execution plans.
In investigations like this, New Relic helps us locate the affected operation, while CloudWatch and RDS metrics provide the surrounding resource context. We then need to connect that operation to the actual code and database query. Before asking AI for a fix, give it the exact error, the affected operation, the timestamp, and the relevant application code. Include what you expected to happen and what happened instead.
A useful starting question is:
What does this evidence establish, which explanations remain possible, and what should we inspect next to distinguish them?
This encourages an investigation that can progress as new evidence arrives.
Look below the abstraction
Our product search used Django’s case-insensitive lookup:
query = query.filter(product_name__icontains=value)In the configuration we investigated, the generated PostgreSQL expression was:
UPPER(product_name::text) LIKE UPPER('%term%')We had created trigram indexes on the raw columns, using these indexed expressions:
product_name gin_trgm_ops
itemid gin_trgm_opsThe important detail was the UPPER() transformation.
The product-name index covered the raw column, while the query searched its uppercase representation. Our EXPLAIN output showed that this query form bypassed the product-name trigram index. PostgreSQL read the tenant’s candidate products and applied the text filter row by row.
This explains why checking that an index exists is insufficient. We need to understand whether the query can use it.
There is a second question: what work does using that index actually require? In the MySQL index change that sent our database CPU to 100%, the problem was the cost of the selected access path. Here, the query expression prevented PostgreSQL from using the intended index. Both investigations needed the query plan to explain what the database was doing.
It also gives AI a much more specific problem. With the ORM code alone, it has limited evidence. With the generated SQL, index definition, and query plan together, we can ask it to examine the relationship between them.
So I followed up with a prompt along these lines:
Compare the expression being filtered with the expression being indexed. Explain which plan nodes support an index mismatch, and identify any assumptions that still need verification.
The useful principle is to ask the assistant to connect its explanation to observable details. We want to understand what happened beneath the abstraction that reported the failure.
Make a change that preserves behaviour
Our fix aligned the query with the existing index.
Moving from icontains to contains removed the UPPER() transformation. We also configured case-insensitive collation on the product-name column to preserve the intended search behaviour.
Both parts mattered. A query becoming faster would not be sufficient if it stopped returning products that users expected to find.
I would treat an AI-generated recommendation with the same requirement: explain both the performance mechanism and the behaviour that must remain unchanged.
In this case, that means checking case sensitivity, representative search terms, and the returned results alongside the execution plan. The lookup and collation changes describe our tested configuration; they are not a general recommendation to replace every icontains call.
PostgreSQL supports trigram-based indexing for LIKE and ILIKE, but effectiveness depends on the query and the trigrams that can be extracted from its search pattern. PostgreSQL pg_trgm documentation.
The plan and the result checks are what let us move from a plausible change to a supported one.
Validate the explanation as well as the fix
We validated the revised queries in PostgreSQL staging against a company with approximately 1.15 million products.
The raw-column LIKE queries used the existing GIN trigram indexes and completed in 77–90 ms. The previous UPPER(...) LIKE UPPER(...) queries exceeded our 12-second database timeout.
That gives a lower-bound improvement of more than 130× for these measured cases. The exact speed-up is unknown because we stopped the old queries before they completed(timed out).
This is where an AI-assisted investigation needs a feedback loop. Return the new plan, timings, and correctness results to the assistant. Ask whether they support the proposed explanation and what remains unresolved. Sometimes an anomaly can be attributed to a combination of reasons. AI helps you better calibrate if the new data conforms to the fixes we introduced.
Carry the method into the next error
The final finding was SPECIFIC to this scenario : our query expression and index expression did not match. However the AI assisted debugging method applies more broadly.
When using AI to investigate an unfamiliar error or stack trace, start with the failing operation. Gather the relevant code and runtime evidence. Ask for explanations that can be tested, then bring the results back into the investigation.
Google’s Effective Troubleshooting chapter describes this process as forming hypotheses and testing them against observations. AI fits naturally into that process when its explanations remain tied to evidence. Google SRE: Effective Troubleshooting.
For a lean engineering team, the lasting value is in making the investigation repeatable. Preserve the symptom, the evidence, the change, and the validation checks. The next unfamiliar failure then starts with a better set of questions and a tested way to answer them.