Why Postgres ignores your trigram index: sargability, UPPER(), and an ORM that rewrites your query
10 min
You add a pg_trgm GIN index, the substring query gets no faster, and EXPLAIN shows a sequential scan. The operator is usually not the problem: pg_trgm serves ILIKE from the index perfectly well. What breaks it is a function wrapped around the column, and on Django that function is one your ORM adds for you. Measured at four table sizes, with the three fixes and what each costs.
