Loading page content
Analyze a Postgres query and get index recommendations, including the part people get wrong most often. What order the columns should go in a composite index.
Paste the output of EXPLAIN ANALYZE. Get back the most likely missing indexes, ordered by impact. Heuristic. Verify the suggestion against your actual data distribution before shipping.
CREATE INDEX CONCURRENTLY idx_orders_status_created_at ON orders (status, created_at);
CREATE INDEX CONCURRENTLY in production. Confirm column cardinality before adding indexes (low-cardinality boolean indexes usually make things worse). Run EXPLAIN ANALYZE again post-deployment to confirm.Composite index column order is the highest-value thing to get right and the least intuitive. Postgres can use a leading subset of an index's columns, so an index on (a, b, c) serves queries filtering on a, or on a and b, but not one filtering on b alone. The rule that follows: equality columns first, then the range column, then anything used only for sorting.
The other frequent cause of an unused index is a function applied to the column in the WHERE clause. WHERE lower(email) = ... cannot use a plain index on email. It needs an expression index on lower(email). Casts do the same thing, silently, and EXPLAIN is where you see it.
Indexes are not free. Each one adds write cost on every insert and update and consumes storage, so an unused index is pure overhead. Check pg_stat_user_indexes for indexes with zero scans before adding more.