iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A PostgreSQL trigram index can support substring searches with LIKE and ILIKE, but the index is not guaranteed to serve every Django icontains query. The key is the SQL Django actually sends: a predicate such as UPPER(name::text) LIKE UPPER('%orc%') does not necessarily match a trigram index built on the plain name column. Check the generated SQL, index definition, and execution plan together before changing the schema or search behavior.
What does Django icontains send to PostgreSQL?
Django defines icontains as case-insensitive containment. Its QuerySet documentation illustrates the lookup with SQL equivalent to ILIKE '%Lennon%', while noting that the exact SQL syntax depends on the database backend: Django QuerySet lookups.
That illustration is not a substitute for inspecting your deployed query. Django ticket #32803 records a particular reproduction in which the lookup produced UPPER(name::text) LIKE UPPER('%orc%'). In that test, a plain-column trigram index did not serve the predicate; changing it to ILIKE produced a bitmap index scan: Django ticket #32803. This is an example tied to the ticket’s Django and PostgreSQL setup, not a claim about every release or backend.
The Tool Desk
Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →Why can a trigram index exist but remain unused?
PostgreSQL’s pg_trgm GiST and GIN operator classes support index searches for LIKE and ILIKE, including patterns that do not begin with a fixed prefix: PostgreSQL 16: pg_trgm. But index use depends on the predicate matching what the index can support.
#1 Best Overall
A trigram index on name is an index on that column. A predicate that applies UPPER() to name is an expression on the column. Those are not automatically interchangeable. In ticket #32803, the reproduction paired a plain-column gin_trgm_ops index with an UPPER(name::text) predicate, then observed a different plan after using ILIKE. Compare the predicate’s indexed expression and operator with your actual index definition rather than assuming that the presence of any trigram index is enough.
How to diagnose the query
-
Capture the SQL Django generates
Inspect the actual QuerySet query or capture the SQL sent to PostgreSQL. Look specifically at whether the condition is shaped like
name ILIKE '%term%'or applies a function, such asUPPER(name) LIKE UPPER('%term%'). Do not infer the deployed SQL from documentation examples alone. -
Check the index definition
Confirm which column or expression is indexed and which operator class it uses. Then compare that definition with the expression in the
WHEREpredicate. The Django ticket’s reported mismatch was a plain-columngin_trgm_opsindex alongside a predicate that appliedUPPER()to the column.Special offer. See more information about Outbyte and uninstall instructions. Please review EULA and Privacy policy. -
Read the plan for the exact query
Use
EXPLAINwith the SQL shape and a representative search term to see the plan PostgreSQL chooses. In the ticket’s PostgreSQL 12.6 reproduction, the alteredILIKEquery showed a bitmap heap scan over a bitmap index scan. That establishes what happened in that test—not what your database must do. Plan selection and timing depend on the actual database, data, and workload. -
Consider how many trigrams the pattern can supply
Even when the operator and index are compatible, the search term matters. PostgreSQL extracts trigrams from the pattern: more extractable trigrams make an index search more effective, while a pattern with no extractable trigrams degenerates to a full-index scan, according to the pg_trgm documentation. A short or otherwise uninformative pattern can therefore be a poor index-search candidate.
Should you change the search method?
First establish that the application needs substring containment. icontains asks whether a value occurs within a string, without regard to case. Replacing it with another search method can change which results qualify.
- Keep substring semantics: investigate the generated predicate, its compatibility with the indexed expression and operator class, the search pattern, and the plan for representative data.
- Use full-text search only for term-oriented matching: Django’s PostgreSQL search tools, including
SearchVectorandSearchQuery, are designed around tokenized terms and text-search configurations rather than arbitrary substring containment: Django PostgreSQL full-text search. - Use trigram similarity only when similarity is the goal: similarity-oriented matching is distinct from asking whether a literal substring occurs. PostgreSQL documents trigram operators and functions in pg_trgm.
What the documented example can—and cannot—tell you
Ticket #32803 reports a reproduction on PostgreSQL 12.6 (Ubuntu build, en_US.UTF-8 collation) and includes author-measured timings averaged over three measurements. Those figures describe that local test only; they are not a production benchmark or a prediction for another database. The issue is a closed duplicate and documents a specific contrast, not a rule that every Django icontains query uses UPPER() or that PostgreSQL always chooses a trigram index for a compatible predicate.
Recommended Free Tools
To identify why your query is slow, you need its generated SQL, the relevant index definition, your Django and PostgreSQL versions, representative data and parameters, and the actual execution plan. Without those, the index mismatch is a plausible diagnostic lead—not a confirmed explanation.
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.
