Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallOutdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchA PostgreSQL pg_trgm index can support substring searches, but the index must match the expression and operator in the SQL Django actually sends. In one Django ticket reproduction, the lookup became UPPER(name::text) LIKE UPPER(...), while the trigram index covered the plain column; changing the predicate to ILIKE produced a bitmap index scan. That is a version- and environment-specific example, not a rule for every Django application. Start by inspecting your generated SQL and its execution plan.
What Django means by icontains
Django defines icontains as a case-insensitive containment lookup. Its QuerySet documentation illustrates the SQL as ILIKE '%Lennon%', while noting that the exact SQL syntax varies by database backend. Treat that as an illustration of the lookup’s intent—not a guarantee about the SQL produced by your installed Django version and backend. Django’s QuerySet lookup documentation explains the lookup and its example.
As an Amazon Associate I earn from qualifying purchases.
The first diagnostic question is therefore not simply whether a trigram index exists. It is: what predicate does PostgreSQL receive, and does the index cover the expression being searched?
Why a trigram index can be present but unused
PostgreSQL’s pg_trgm GiST and GIN operator classes support trigram-based searches using LIKE and ILIKE, including patterns that do not begin with a fixed prefix. But an index on a bare column is not interchangeable with an expression on that column. If the predicate applies a function such as UPPER(column), compare that expression with the indexed expression and operator class rather than assuming the plain-column index applies.
#1 Best Overall
Django ticket #32803 documents a concrete reproduction: a plain-column GinIndex using gin_trgm_ops was paired with a predicate shaped like UPPER(name::text) LIKE UPPER(...), and the reported plan did not use the trigram index. In the author’s PostgreSQL 12.6 test setup, changing the query to use ILIKE yielded a bitmap index scan. The ticket is a closed duplicate documenting that particular contrast; it does not establish that all Django releases emit the same SQL or that every compatible query will use an index. Django ticket #32803.
Diagnose the query in your application
-
Capture the SQL Django generated
Inspect the SQL for the actual QuerySet, using the QuerySet’s SQL representation or database logging. Look for whether the predicate is shaped like
column ILIKE '%term%'or instead applies an expression, for exampleUPPER(column) LIKE UPPER('%term%'). Check the SQL produced by your deployed Django version and backend; do not infer it from a documentation example or another application’s ticket. -
Compare the predicate with the index definition
Inspect the database’s index definition. Confirm which column or expression is indexed and which operator class it uses, then compare that with the predicate from step one. A plain-column trigram index and a function applied to the column are different index expressions; whether the index can serve a query depends on that compatibility.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Read the plan for the exact query
Run
EXPLAINfor the SQL with a representative search pattern; useEXPLAIN ANALYZEwhen you can safely execute the query and want observed execution details. Look for an index scan or a bitmap index scan using the trigram index, and distinguish it from a sequential scan or another plan. The planner chooses based on the actual query, data, and workload, so a plan from a ticket or a different dataset cannot predict yours. -
Check whether the pattern yields useful trigrams
Index effectiveness also depends on the search pattern. PostgreSQL extracts trigrams from the pattern; more extractable trigrams generally make the index search more effective. A pattern with no extractable trigrams can degenerate to a full-index scan. This is separate from expression compatibility: even a compatible predicate may not become a selective index search for a particular pattern. PostgreSQL 16’s
pg_trgmdocumentation describes index support and this pattern limitation.
Choose search semantics before changing the lookup
If your requirement is case-insensitive substring containment, diagnose the generated predicate and index fit before replacing the lookup. PostgreSQL full-text search and trigram similarity are distinct search approaches, not interchangeable performance switches. Django’s SearchVector and SearchQuery support token- and configuration-oriented full-text search; trigram similarity is intended for similarity matching. Choose either only if its matching behavior fits the application’s requirements. Django’s PostgreSQL search documentation covers its full-text search tools.
What the ticket’s results do—and do not—show
The ticket reports a local reproduction on PostgreSQL 12.6, built for Ubuntu, with the en_US.UTF-8 collation; its author averaged three measurements. Those timing observations describe that test setup only, not expected performance in another application. The useful diagnostic lesson is the reported contrast between a function-wrapped predicate and an ILIKE predicate—not a production benchmark or a guarantee about planner behavior.
Without your generated SQL, index definition, PostgreSQL and Django versions, data, and query plan, it is not possible to determine why your application did not use its index. Those details let you separate an expression mismatch from a pattern with limited trigram selectivity or a planner choice based on the particular workload.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
