Using EXPLAIN ANALYZE against a Postgres 18 database seeded with 1 million comments, this walkthrough shows how composite index column order determines query performance. Equality columns should come first, then the column used for sorting or ranging, following the leftmost prefix rule. A three-column composite index turns a 16.6ms sequential scan into a 0.04ms index scan, and further tuning cuts a dashboard query with a LATERAL join from 436ms to 0.5ms. It also covers the storage and write costs of adding indexes, and when a composite index makes a single-column index redundant.
Table of contents
What Is a SQL Index?Start With the Query, Not the TableReading the First PlanColumn Order Is EverythingThe Query Our New Index Can't ServeWhat Do Indexes Cost?SummaryFrequently Asked QuestionsQuestions this post answers
What column order should I use for a composite index in Postgres?
Put equality columns first, then the column you sort or range on. For a query filtering on issue_id and user_id while ordering by created_at, an index on (issue_id, user_id, created_at DESC) moves all three conditions into the index condition and removes the sort step entirely, dropping execution time from 16.6ms to 0.04ms on a 1-million-row table. Developers tuning slow queries can find deep-dive database performance breakdowns like this on daily.dev.
Why is Postgres still running a sort even though my query uses an index?
The index likely returns rows in the wrong order for that query. A dashboard query using a LATERAL join hit a composite index ordered (issue_id, user_id, created_at DESC), which sorts by user_id before created_at, forcing Postgres to sort once per outer row, 6,537 times, pushing execution to 436ms until a differently ordered index fixed it. Anyone debugging unexpected sort nodes in query plans can track patterns like this via daily.dev.
What is the leftmost prefix rule for composite indexes in Postgres?
A composite index only serves queries that filter using its leading columns in order. With an index on (issue_id, user_id, created_at DESC), filtering on issue_id alone works, and issue_id plus user_id works, but filtering on user_id alone does not, because those values are scattered throughout the index rather than grouped together. Engineers designing index strategies can keep up with practical Postgres indexing guides on daily.dev.
50.3K Impressions1 Comment