Why Postgres ignores your trigram index: sargability, UPPER(), and an ORM that rewrites your query

You have a table of documents and you need to find the ones containing a word. You do the reading, you install pg_trgm, you build the GIN index everybody recommends, and you run the query again.

It takes exactly as long as it did before.

EXPLAIN says Seq Scan. The index is there, it’s valid, ANALYZE has run, and the planner will not touch it. I spent an evening on this in a side project that watches Brazil’s official gazette, and the answer was not the one I went looking for.

The operator is probably not your problem

The folklore says ILIKE can’t use an index, so you rewrite everything as LIKE and move on. The folklore is out of date. From the pg_trgm documentation:

These index types support the above-described similarity operators, and additionally support trigram-based index searches for LIKE, ILIKE, ~, ~* and = queries.

ILIKE is on that list, and has been since PostgreSQL 9.1. I measured it on 100,000 rows of realistic document text, with one rare needle in every 500 rows: search_text ILIKE '%saneamento%' comes back in 2.4 ms on a bitmap index scan. So does plain LIKE. The operator was never the villain.

Here is the query that was actually slow, on the same table, with the same index, looking for the same rows:

 Aggregate  (cost=9193.02..9193.03 rows=1) (actual time=255.469..255.470 rows=1)
   ->  Seq Scan on bench_act  (cost=0.00..9193.00 rows=10) (actual time=2.338..255.450 rows=200)
         Filter: (upper(search_text) ~~ '%SANEAMENTO%'::text)
         Rows Removed by Filter: 99800
 Execution Time: 255.493 ms

And the fast one:

 Aggregate  (cost=563.57..563.58 rows=1) (actual time=3.078..3.079 rows=1)
   ->  Bitmap Heap Scan on bench_act  (cost=524.50..563.54 rows=10) (actual time=2.463..3.070 rows=200)
         Recheck Cond: (search_text ~~ '%saneamento%'::text)
         ->  Bitmap Index Scan on bench_act_trgm  (cost=0.00..524.49 rows=10) (actual time=2.443..2.443 rows=200)
               Index Cond: (search_text ~~ '%saneamento%'::text)
 Execution Time: 3.139 ms

Two words in there tell you everything. Index Cond means the condition was handed to the index. Filter means the condition is being evaluated by hand, row by row, after the rows were already read. Rows Removed by Filter: 99800 is the machine telling you it looked at every row and threw almost all of them away.

The difference between the two queries is the upper(...) in the filter line.

What “sargable” means, and why a function breaks it

Sargable is old database jargon, from Search ARGument ABLE: a predicate the engine can turn into an index lookup rather than a per-row test. It is a property of the whole predicate, not of the operator in it.

A pg_trgm index on search_text stores the trigrams of the values in search_text. Nothing else. When your WHERE clause says search_text LIKE '%x%', the planner can compare that against what the index holds, extract the trigrams of x, and go look them up.

When your WHERE clause says UPPER(search_text) LIKE '%X%', it is asking about a different value. The index has no idea what UPPER(search_text) is for any row. Postgres will not assume that a function is order-preserving, or that it can invert it, or that the trigrams of a string tell it anything about the trigrams of its uppercased form. So it does the only correct thing available: read every row, compute the function, test the result.

The rule is that the planner matches the expression in your query against the expression the index was built on. That’s stated plainly in the docs on indexes on expressions, which exist precisely because of this:

This query can use an index if one has been defined on the result of the lower(col1) function

You’ve met the same rule before under a different name. B-tree indexes support LIKE, but the docs are careful about when:

The optimizer can also use a B-tree index for queries involving the pattern matching operators LIKE and ~ if the pattern is a constant and is anchored to the beginning of the string

col LIKE 'foo%' works, col LIKE '%bar' doesn’t, and it isn’t because LIKE is bad. It’s because an ordered index can only jump to a known prefix. Same idea, different index type: the index answers one shape of question, and your predicate either is that shape or isn’t.

Worth knowing which wrappers actually count. Django’s __contains emits search_text::text LIKE %s, and that cast is a no-op on a text column, so Postgres drops it before planning and the index is used. UPPER() is not a no-op, so it isn’t dropped. A function around your column is a problem when it changes the value.

Nobody wrote that UPPER() by hand

Which brings up the part I found genuinely annoying. In my case the SQL with upper() in it was not SQL I wrote. It was Model.objects.filter(search_text__icontains=term).

Django’s PostgreSQL backend keeps a table of how each lookup becomes SQL. It is exactly what it looks like (Django 5.2, django/db/backends/postgresql/base.py):

operators = {
    "exact": "= %s",
    "iexact": "= UPPER(%s)",
    "contains": "LIKE %s",
    "icontains": "LIKE UPPER(%s)",
    ...
}

That’s the right-hand side. The left-hand side gets the same treatment a few files over, in operations.py:

# Use UPPER(x) for case-insensitive lookups; it's faster.
if lookup_type in ("iexact", "icontains", "istartswith", "iendswith"):
    lookup = "UPPER(%s)" % lookup

So __icontains never becomes ILIKE on PostgreSQL. It becomes UPPER(column) LIKE UPPER(pattern), and the wrapper that makes the query non-sargable is put there by the framework, in a file most people using the ORM will never open. My index was fine. My operator was fine. The seven characters that made the difference, UPPER( and the bracket that closes it, were generated.

An ORM has taken a query plan off me before, in a benchmark that could not see its own N+1. The pattern is the same: the thing you wrote and the thing the database ran are different objects, and only one of them is in your editor.

The comment is correct, and it is still the wrong trade

# it's faster deserves a fair test rather than a snort, so I gave it one. I forced both forms to a sequential scan, so neither could use an index, and timed them over the same rows.

It is faster. Between 5% and 18% faster across the four sizes, consistently, in the direction the comment claims. On a sequential scan, comparing pre-uppercased bytes beats ILIKE’s locale-aware case folding, and somebody clearly measured that.

The form that wins that 5% is also the form that cannot use the index, so the saving is bought with a factor of a hundred. On the left panel the two sequential scans are indistinguishable from each other, and both sit two orders of magnitude above everything the index served.

I don’t think that’s a bug in Django, for what it’s worth. The backend has to pick one translation for __icontains without knowing what indexes you have, and pre-folding the case is a defensible default for a table with no trigram index at all, which is most tables. It’s just a default that goes quietly wrong the moment you add the one index that would have helped, which is the same shape as a cheaper model that turned out to cost more: right on the axis somebody measured, wrong on the one that ended up mattering.

Three ways out, and what each one costs

Normalise the column and use __contains. If you can decide the casing on write, do. The table I hit this on already stored lowercased, accent-stripped text for other reasons, so the case-insensitivity that __icontains provides was buying nothing at all. Switching the lookup to __contains took the query from a sequential scan to a bitmap index scan and changed no results. This is the cheapest fix and the one that needs you to own the write path.

Index the expression the ORM is actually going to ask about. If you can’t touch the query, build the index over UPPER(col) instead:

CREATE INDEX bench_act_upper_trgm
    ON bench_act USING gin (UPPER(search_text) gin_trgm_ops);

Measured on the same 100,000 rows, this takes __icontains from 254.7 ms to 2.4 ms, matching the plain LIKE path within noise. It is the pink line on the left panel. The cost is on the other side of the ledger, and the docs say it out loud:

Index expressions are relatively expensive to maintain, because the derived expression(s) must be computed for each row insertion and non-HOT update.

You are also now carrying two trigram indexes over the same text if you kept the first one, and trigram indexes over long text are not small. On the project I was working on, one of them was 67 MB against 165 MB of actual data.

Write the predicate yourself. A custom lookup, or Func, or dropping to .extra/raw for the one query that matters, gets you plain ILIKE, which pg_trgm serves from the index. I did not take this route so I’m not going to pretend I know its rough edges, but it is the option that keeps both the case-insensitivity and the index.

The habit worth taking away

Read EXPLAIN for the words, not the timings. Index Cond and Filter tell you which side of this line you’re on, and Rows Removed by Filter tells you the size of the mistake, both of them before you’ve made the table big enough for the timing to hurt.

And the general form of the trap has nothing to do with trigrams. WHERE date(created_at) = '2026-09-01' will not use your index on created_at. WHERE lower(email) = ? will not use your index on email. Any function wrapped around an indexed column asks the planner a question the index cannot answer, and the fix is always the same two options: move the function to the other side of the comparison, or build an index on the function.

The part that got me was that I hadn’t written the function. If you use an ORM, it is worth logging the SQL for your three slowest queries once and reading what actually came out. Mine were not the queries I thought I had.

The measurement harness, the schema and the raw timings are in web/scripts/plots/why-postgres-ignores-your-trigram-index/ in this site’s repository; it needs a throwaway Postgres 16 and one shell command. If you’ve hit a version of this where the fix was something other than these three, I’d like to hear it, particularly on the custom-lookup route. The narrative version, including the twenty minutes I spent building an index I then failed to use, is in the build log.

References

• PostgreSQL — pg_trgm — the GiST and GIN operator classes, and the list of operators they serve from the index, ILIKE included.
• PostgreSQL — Indexes on Expressions — the lower(col1) example, and the maintenance cost of an expression index.
• PostgreSQL — Index Types — when a B-tree can serve LIKE, and why an unanchored pattern is not one of those times.
• PostgreSQL — Using EXPLAIN — how to read the plan nodes the two queries above produce.
• Django — django/db/backends/postgresql/base.py — the operators table that turns __icontains into LIKE UPPER(%s).
• Django — django/db/backends/postgresql/operations.py — lookup_cast, which wraps the column side, comment included.