---
title: "SQL Indexing Explained: Composite Indexes and Column Order"
url: https://daily.dev/posts/sql-indexing-explained-composite-indexes-and-column-order-exxme3lqv
source_url: https://milanjovanovic.tech/blog/how-to-design-the-right-sql-index
type: article
source: "Milan Jovanović"
published: 2026-08-21T21:47:25.955Z
updated: 2026-08-21T21:47:50.012Z
tags: ["database", "sql", "postgresql"]
reading_time: 8
upvotes: 32
comments: 1
language: 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 · 32 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: <https://milanjovanovic.tech/blog/how-to-design-the-right-sql-index>

## Questions 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._

## Community discussion

Top comments from developers on daily.dev.

**@pdfopsdev** · 3 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

- [Subtleties of SQLite Indexes](https://daily.dev/posts/subtleties-of-sqlite-indexes-xsre2szh7) · Hacker News · 1 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)