Postgres query slow? When to add an index, and when it makes things worse
An index speeds up reads and taxes every write. Here is when it pays, how to order the columns, and how to read EXPLAIN, using the PostgreSQL docs…
Aliteq
Voltage · Hardware Editor
The short answer
Add a Postgres index when a query keeps filtering, joining or sorting a big table on the same columns and returns only a small slice of it. Skip it on small tables, on columns most queries ignore,…
Helps: selective filters, join columns such as foreign keys, and ORDER BY with LIMIT
Hurts: write-heavy tables, tiny tables, and indexes nothing ever uses
Composite order: equality columns first, then the range or sort column
Proof: run EXPLAIN (and EXPLAIN ANALYZE) before and after, and drop the index if the plan does not improve
Aliteq
Read the full story
Postgres query slow? When to add an index, and when it makes things worse