--- title: SQL Indexing Explained: Composite Indexes and Column Order description: Using EXPLAIN ANALYZE against a Postgres 18 database seeded with 1 million comments, this walkthrough shows how composite index column order determines query... canonical: https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: SQL Indexing Explained: Composite Indexes and Column Order | daily.dev og:description: Using EXPLAIN ANALYZE against a Postgres 18 database seeded with 1 million comments, this walkthrough shows how composite index column order determines query... og:url: https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv og:image: https://api.daily.dev/og/posts/exxMe3LqV.png og:image:alt: SQL Indexing Explained: Composite Indexes and Column Order og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # SQL Indexing Explained: Composite Indexes and Column Order **[Milan Jovanović](https://daily.dev/sources/milanjovanovic)** · 8 min read · 42 upvotes · 1 comments ## Summary 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. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### What column order should I use for a composite index in PostgreSQL? Put equality columns first, then the column used for sorting or range filtering. On a demo query filtering by issue_id and user_id and sorting by created_at, an index on (issue_id, user_id, created_at DESC) moved all three conditions into the index condition and eliminated the sort step, cutting execution time from 16.6ms to 0.04ms. _Nail composite index ordering before it costs you production latency, with guidance daily.dev surfaces for postgresql performance work._ ### Why is PostgreSQL still running a sort even though my query uses an index? The index returns rows in the wrong order for that query. A dashboard query using an index on (issue_id, user_id, created_at DESC) had to sort by user_id before created_at, forcing Postgres to run a separate sort once per open issue, 6,537 times in one case, pushing execution time to 436 milliseconds instead of under a millisecond. _Debugging unexpected sorts in query plans is easier when postgresql troubleshooting resources are one search away on daily.dev._ ### What is the leftmost prefix rule for composite indexes? A composite index can only serve queries that filter on its leading columns in order. For an index on (issue_id, user_id, created_at DESC), a query filtering on issue_id alone works, and issue_id plus user_id works, but filtering on user_id alone does not benefit because those values are scattered across the whole index tree rather than grouped together. _Understanding index internals like this pays off when tuning postgresql schemas, a topic daily.dev keeps developers current on._ ## Community discussion Top comments from developers on daily.dev. **@pdfopsdev** · 4 upvotes > the leftmost prefix rule bites hardest with ORMs that auto-generate one index per column, you end up with five single-column indexes and none of them serve a composite query. worth checking pg_stat_user_indexes for idx_scan=0 rows before adding yet another one. ## Similar posts on daily.dev - [When Postgres Picks the Wrong Index \(And How to Fix It\)](https://daily.dev/posts/when-postgres-picks-the-wrong-index-and-how-to-fix-it--fqpb6k8mg) · Milan Jovanović · 8 upvotes · 0 comments - [Things you didn't know about indexes](https://daily.dev/posts/things-you-didn-t-know-about-indexes-a4znm2ugw) · Lobsters · 0 upvotes · 0 comments - [Database Animations: The Interview Question Everybody Gets Wrong](https://daily.dev/posts/database-animations-the-interview-question-everybody-gets-wrong-gbyx7dzkb) · Brent Ozar · 0 upvotes · 0 comments - [How Database Indexes Work – A Practical Guide with PostgreSQL Examples](https://daily.dev/posts/how-database-indexes-work-a-practical-guide-with-postgresql-examples-8anfbdgcl) · freeCodeCamp · 72 upvotes · 0 comments - [Introduction to PostgreSQL Indexes ::](https://daily.dev/posts/introduction-to-postgresql-indexes--peasixbnz) · Hacker News · 0 upvotes · 0 comments --- Tags: [#database](https://daily.dev/tags/database), [#sql](https://daily.dev/tags/sql), [#postgresql](https://daily.dev/tags/postgresql) [View this post on daily.dev](https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"SQL Indexing Explained: Composite Indexes and Column Order","url":"https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv"},"datePublished":"2026-08-21T21:47:25.955Z","dateModified":"2026-09-13T19:58:07.327Z","description":"Using EXPLAIN ANALYZE against a Postgres 18 database seeded with 1 million comments, this walkthrough shows how composite index column order determines query...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/0551f42849a8e2769e9903f7f80f6bea?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/0551f42849a8e2769e9903f7f80f6bea?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Milan Jovanović","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Milan Jovanović","logo":"https://media.daily.dev/image/upload/t_logo,f_auto/v1/logos/78d8abfb692746538410dee47f93e566","url":"https://daily.dev/sources/milanjovanovic"},"commentCount":1,"discussionUrl":"https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":42},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":1}],"keywords":"database,sql,postgresql","timeRequired":"PT8M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Milan Jovanović","item":"https://daily.dev/sources/milanjovanovic"},{"@type":"ListItem","position":3,"name":"SQL Indexing Explained: Composite Indexes and Column Order"}]} {"@context":"https://schema.org","@type":"WebPage","@id":"https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv","comment":[{"@type":"Comment","text":"the leftmost prefix rule bites hardest with ORMs that auto-generate one index per column, you end up with five single-column indexes and none of them serve a composite query. worth checking pg_stat_user_indexes for idx_scan=0 rows before adding yet another one.","datePublished":"2026-08-22T18:04:33.697Z","url":"https://daily.dev/posts/exxMe3LqV#c-6WFbWvRPn","author":{"@type":"Person","name":"PDFops","url":"https://daily.dev/pdfopsdev","image":"https://media.daily.dev/image/upload/s---8isRBKc--/f_auto/v1782922291/avatars/avatar_orjMeK8QKaaVZwGq7ScPz?_a=BAMAMicg0"},"interactionStatistic":{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":4}}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv#faq","mainEntity":[{"@type":"Question","name":"What column order should I use for a composite index in PostgreSQL?","acceptedAnswer":{"@type":"Answer","text":"Put equality columns first, then the column used for sorting or range filtering. On a demo query filtering by issue_id and user_id and sorting by created_at, an index on (issue_id, user_id, created_at DESC) moved all three conditions into the index condition and eliminated the sort step, cutting execution time from 16.6ms to 0.04ms. Nail composite index ordering before it costs you production latency, with guidance daily.dev surfaces for postgresql performance work."}},{"@type":"Question","name":"Why is PostgreSQL still running a sort even though my query uses an index?","acceptedAnswer":{"@type":"Answer","text":"The index returns rows in the wrong order for that query. A dashboard query using an index on (issue_id, user_id, created_at DESC) had to sort by user_id before created_at, forcing Postgres to run a separate sort once per open issue, 6,537 times in one case, pushing execution time to 436 milliseconds instead of under a millisecond. Debugging unexpected sorts in query plans is easier when postgresql troubleshooting resources are one search away on daily.dev."}},{"@type":"Question","name":"What is the leftmost prefix rule for composite indexes?","acceptedAnswer":{"@type":"Answer","text":"A composite index can only serve queries that filter on its leading columns in order. For an index on (issue_id, user_id, created_at DESC), a query filtering on issue_id alone works, and issue_id plus user_id works, but filtering on user_id alone does not benefit because those values are scattered across the whole index tree rather than grouped together. Understanding index internals like this pays off when tuning postgresql schemas, a topic daily.dev keeps developers current on."}}]} ```