Django's icontains steered Postgres around a trigram index by wrapping the column in UPPER()

Django's call was `query.filter(product_name__icontains=value)` ^[3]. In the configuration the team investigated, it produced `UPPER(product_name::text) LIKE UPPER('%term%')` ^[4]. Their index was declared as `product_name gin_trgm_ops`, on the bare column, with a second trigram index on `itemid` ^[5]. So the query asked Postgres to search one expression while the index covered another ^[2]. "The product-name index covered the raw column, while the query searched its uppercase representation," the post's author wrote ^[6].

Row-by-row filtering costs more for every product a tenant holds ^[1]. The team recorded its comparison at about 4,908 requests per minute on the same db.r6i.large instance class ^[8]. The author is careful with that number, writing that similar throughput and instance size "still leave differences in query mix, configuration, and execution plans" ^[9]. For the several-second searches to reproduce on another system, two conditions have to hold. The predicate has to be one the trigram index cannot serve. And tenants have to hold enough products that one pass over them takes seconds ^[1].

The team kept its index and changed the query to fit it ^[10]. The post does not say whether an index on the uppercase expression was considered, and it does not give the Postgres or Django versions or the collation definition. I think keeping the index is the sound choice when case-insensitivity belongs to one column. The rule then lives once in the schema instead of at every call site. Correctness now rests on the collation the team set on product_name ^[11]. "Both parts mattered. A query becoming faster would not be sufficient if it stopped returning products that users expected to find," the author wrote ^[12]. The checks the post lists are case sensitivity, representative search terms and the results returned ^[13].

Claude appears in the post's title, and the author used it to analyse parts of the stack ^[14]. The diagnosis came from the generated SQL, the index definition and the query plan, read together ^[15]. On the assistant working from ORM code alone, the author wrote: "it has limited evidence" ^[15]. The follow-up prompt would get a good answer from any reviewer who has read a query plan: "Compare the expression being filtered with the expression being indexed" ^[16].