QuerySet.explain(): make it make sense.
summary
Explains how PostgreSQL chooses index vs sequential scan using the cost model (seq_page_cost=1, random_page_cost=4 by default), shows using EXPLAIN ANALYZE from Django QuerySet.explain() to compare estimated vs actual rows and time, demonstrates fixes: expression indexes like upper(col) for case‑insensitive search, pg_trgm gin index plus extension for ILIKE/contains, partial indexes for low‑cardinality filters, must run ANALYZE after creating indexes, JSON index needs correct cast/type, and splitting OR queries into separate indexed subqueries yields ~5× speedup.
AI-written summary · about
chapters (7)
- 0:00Introduction and background
- 1:35Database internals and indexing
- 11:10QuerySet.explain() basics
- 18:38Case-insensitive and GIN indexes
- 23:56Combining multiple indexes
- 26:03Performance tips and cheat sheet
- 27:12Q&A on migrations and analysis
related talks
32mEXPLAIN: make it make sense
40mExplaining the Postgres Query Optimizer
29mExplaining the Postgres Query Optimizer
51mExplaining the Postgres Query Optimizer
26mBeyond Joins and Indexes
52mA Deep Dive into PostgreSQL Indexing
46mPostgreSQL Beyond Django: Strategies to Get Max Performance with Álvaro Justen
45mA Deep Dive into PostgreSQL Indexing