Composite Indexes in PostgreSQL & MySQL: Column Order Guide

Adding three separate single-column indexes on tenant_id, status, and created_at will not make a multi-filter dashboard query fast—in fact, putting columns in the wrong order inside a composite B-Tree index stops the database engine from narrowing the search range right after the first range predicate. To eliminate disk-heavy heap fetches and in-memory filesort operations in PostgreSQL and MySQL, you must order composite index columns by the Equality -> Sort -> Range (ESR) rule rather than simply listing the most selective column first. Below is how composite B-Tree traversal actually works on disk, with real EXPLAIN ANALYZE plans from production workloads.

Why Separate Single-Column Indexes Lose to One Composite Index

When our engineering team at Loop Origin audits a slow SaaS or e-commerce database, we almost always find tables where every foreign key and status column has its own individual index, yet a basic orders query takes 850 milliseconds. Consider a multi-tenant orders table with 18 million rows and this common dashboard query:

SELECT id, customer_id, total_cents, created_at
FROM orders
WHERE tenant_id = 42
  AND status = 'paid'
  AND created_at >= '2026-10-01 00:00:00'
ORDER BY created_at DESC
LIMIT 25;

If you only have separate indexes on (tenant_id), (status), and (created_at), the query planner has two bad choices:

1. Scan the (tenant_id) index, fetch all 120,000 matching rows for tenant 42 from the table heap, filter status = 'paid' and created_at >= '2026-10-01' in memory, and sort the survivors.
2. Combine two indexes via a BitmapAnd (PostgreSQL) or index_merge (MySQL), which still requires reading thousands of bitmaps and sorting before applying LIMIT 25.

A single B-Tree index is a sorted, hierarchical phonebook where each key is compared left to right: first by Column 1, then—only when Column 1 values are identical—by Column 2, and when both are identical, by Column 3.

The Equality -> Sort -> Range (ESR) Rule Explained

A widespread myth in backend interviews is that you should always put the highest-cardinality (most selective) column first in a composite index. That advice holds only when every filter in your WHERE clause is an exact equality (=) match. The moment a query mixes equality filters (=), range filters (>, <, BETWEEN), and ORDER BY, B-Tree tree geometry dictates the column order.

Once the B-Tree encounters a range condition (created_at >= '2026-10-01'), all subsequent columns in the index are no longer globally sorted across the matched slice. Look at what happens when we compare two candidate composite indexes for the query above:

Index Definition	Can Use tenant_id Prefix?	Can Filter status = 'paid' in Index?	Can Read created_at in Sorted Order Without Sorting?	Execution Result on 18M Rows
INDEX (tenant_id, created_at, status)	Yes (Equality)	No (Only as an in-index filter scan after range)	Yes, but must scan every status row in date order	Scans thousands of pending/cancelled rows in the date range (~48 ms)
INDEX (created_at, tenant_id, status)	No (Starts with range)	No	Scans global recent orders across all tenants	Horrible for sparse tenants (~390 ms)
INDEX (tenant_id, status, created_at DESC)	Yes (Equality #1)	Yes (Equality #2)	Yes—walks backward from the newest (42, 'paid') leaf node and stops after 25 rows	Sub-millisecond (0.42 ms)

With (tenant_id, status, created_at DESC), the B-Tree jumps straight to the (42, 'paid') sub-tree. Inside that sub-tree, every entry is already ordered by created_at DESC. Because our query also asks for created_at >= '2026-10-01' and ORDER BY created_at DESC LIMIT 25, the engine reads the first 25 leaf entries and halts immediately—zero sorting, zero wasted rows.

Reading the EXPLAIN ANALYZE Proof in PostgreSQL

Let's inspect the actual PostgreSQL execution plan before and after applying the ESR composite index.

Before: Wrong Column Order (tenant_id, created_at, status)

Limit  (cost=0.56..142.80 rows=25 width=28) (actual time=0.19..46.84 rows=25 loops=1)
  ->  Index Scan Backward using idx_orders_tenant_created_status on orders
        (cost=0.56..49820.10 rows=8740 width=28) (actual time=0.18..46.81 rows=25 loops=1)
        Index Cond: ((tenant_id = 42) AND (created_at >= '2026-10-01 00:00:00'::timestamp))
        Filter: ((status)::text = 'paid'::text)
        Rows Removed by Filter: 14820
        Buffers: shared hit=11420 read=1840
Planning Time: 0.24 ms
Execution Time: 46.89 ms

Look closely at Rows Removed by Filter: 14820 and Buffers: shared hit=11420 read=1840. Because created_at (a range) was placed before status (an equality), PostgreSQL had to walk through 14,820 refunded and cancelled orders inside that date window to find 25 paid ones.

After: ESR Column Order + Covering Columns (tenant_id, status, created_at DESC) INCLUDE (customer_id, total_cents)

In PostgreSQL 11+, adding INCLUDE (customer_id, total_cents) appends non-key payload columns to the leaf pages so the database does not even need to visit the table heap (an Index-Only Scan):

CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_cover
ON orders (tenant_id, status, created_at DESC)
INCLUDE (id, customer_id, total_cents);

Here is the resulting EXPLAIN (ANALYZE, BUFFERS) output:

Limit  (cost=0.56..4.12 rows=25 width=28) (actual time=0.04..0.38 rows=25 loops=1)
  ->  Index Only Scan using idx_orders_tenant_status_created_cover on orders
        (cost=0.56..1245.10 rows=8740 width=28) (actual time=0.04..0.36 rows=25 loops=1)
        Index Cond: ((tenant_id = 42) AND (status = 'paid') AND (created_at >= '2026-10-01 00:00:00'::timestamp))
        Heap Fetches: 0
        Buffers: shared hit=5
Planning Time: 0.19 ms
Execution Time: 0.42 ms

Buffers: shared hit=5 and Execution Time: 0.42 ms—a 110x speedup and a 99.96% reduction in buffer reads.

How This Connects to Concurrency, Locks, and Senior Interviews

Bad indexes do more than slow down SELECT queries—they also widen lock scope during transactions. In MySQL InnoDB (REPEATABLE READ), an UPDATE or SELECT ... FOR UPDATE locks every index record it scans. If your query lacks a tight composite index, InnoDB locks thousands of unrelated rows (or the entire table via gap locks), causing deadlocks under load. That is why we always pair composite index tuning with explicit lock boundaries, like the patterns I demonstrated in how to prevent double booking with locks, holds, and expiry.

When we interview senior backend engineers or build high-throughput platforms through our custom web application development services at Loop Origin, reading an EXPLAIN plan and spotting redundant prefixes is one of our favorite practical exercises—right alongside the production scenarios in my Laravel interview questions with answers from real projects.

Production Composite Index Audit Checklist

Before merging any migration that adds or alters an index on a large table, run through these six checks:

• [ ] Apply the ESR Order: Place exact equality columns (=) first, sort columns (ORDER BY) second, and range columns (>, <, BETWEEN) last.
• [ ] Order Equality Columns by Selectivity & Prefix Reuse: If both tenant_id and status use =, put tenant_id first if it has higher selectivity and lets other queries (WHERE tenant_id = ?) reuse the left prefix without needing a separate single-column (tenant_id) index.
• [ ] Drop Redundant Left-Prefix Indexes: Once you create (tenant_id, status, created_at), an older single-column index on (tenant_id) is completely redundant and slows down every INSERT, UPDATE, and DELETE.
• [ ] Consider Partial Indexes for Skewed Statuses (PostgreSQL): If 95% of your rows have status = 'completed' and your hot worker query only looks for status = 'pending', create a tiny partial index: CREATE INDEX CONCURRENTLY idx_jobs_pending ON jobs (run_at) WHERE status = 'pending';.
• [ ] Always Build Indexes Online: Use CREATE INDEX CONCURRENTLY in PostgreSQL or ALGORITHM=INPLACE, LOCK=NONE in MySQL 8.0 so you never block live writes on production tables.
• [ ] Verify Buffer Reads, Not Just Milliseconds: Run EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL (or EXPLAIN ANALYZE in MySQL 8.0.18+) because a small staging table fits in RAM and hides terrible page-read counts that will stall production.

If your team is scaling a database-heavy SaaS, ERP, or marketplace application and needs hands-on query optimization or infrastructure tuning, reach out through our server setup and maintenance team at Loop Origin.

Frequently Asked Questions (FAQ)

Does the order of columns in the SQL WHERE clause affect whether the composite index is used?

No. Both PostgreSQL and MySQL query optimizers reorder predicates in the WHERE clause automatically during query planning. What matters is the column order defined inside the CREATE INDEX (col1, col2, col3) statement itself.

Does an index on (a, b, c) also speed up queries that only filter on b and c?

Generally no, because a B-Tree is sorted primarily by a. Unless a has very few distinct values and the database engine supports an Index Skip Scan (available in MySQL 8.0.13+ for low-cardinality leading columns, and only in specific cases), a query filtering only on WHERE b = 10 cannot jump directly to matching entries in (a, b, c).

How does IN ('paid', 'processing') behave in the ESR rule—is it Equality or Range?

An IN (...) list with a small number of constants is treated by modern optimizers as multiple equality lookups, meaning the engine can still traverse subsequent index columns for each value in the IN list. However, if you also have an ORDER BY created_at DESC after an IN (...) clause on status, the engine may have to merge-sort the streams from 'paid' and 'processing'.

When should I use INCLUDE columns instead of adding them as key columns in PostgreSQL?

Use INCLUDE (col4, col5) when those columns only appear in the SELECT list (to achieve an Index-Only Scan) and are never used in WHERE or ORDER BY clauses. Keeping them out of the B-Tree branch keys shrinks non-leaf index pages and allows you to include data types that lack B-Tree comparison operators.

How many composite indexes are too many on a high-write table?

Every secondary index adds write amplification to INSERT, DELETE, and non-HOT UPDATE operations. On write-heavy tables (such as order events, audit logs, or queue payloads), aim to keep the total index count between 3 and 5 by consolidating overlapping indexes using left-prefix reuse and INCLUDE columns.