How to confirm that the index you added actually improves query performance
summary
Automatic btree index on a text field does not get used because Django ORM emits LIKE with leading and trailing wildcards so the planner chooses a parallel sequential scan even on a representative sample with 100,000 rows filtered. Replacing the btree with a gin trigram op class drops a node cost from about 81,000 to 235 and produces a bitmap index scan. Fixing case insensitive searches requires an index built with upper(...) so the index actually gets used.
AI-written summary · about
chapters (9)
- 0:00Confirming index performance impact
- 1:54PostgreSQL storage internals
- 5:48Sequential vs index scans
- 9:36B-tree indexes limitations
- 13:30Why indexes aren’t used
- 17:25Non-working LIKE index in Django
- 20:36GIN indexes for partial matching
- 23:04Case-insensitive partial matching
- 25:54Index selection summary
related talks
37mRules of Indexing in PostgreSQL
29mPostgres Index types and where to find them
33mHow to Make Your Backend Roar
49mFlexible Indexing with Postgres
37mRules of Indexing in PostgreSQL
53mAdvanced Indexing Techniques in PostgreSQL
46mA Deep Dive Into PostgreSQL Indexing
45mA Deep Dive into PostgreSQL Indexing