A train chart, sorted by coach, then by berth within each coach
“Who’s in S4?” is one block. “Berth 23?” is one jump per coach. Names aren’t sorted, so “Mr Iyer?” reads every line.
Index (user_id, created_at, status). Can status = 'FAILED' seek?
Bad: Yes, status is indexed
FAILED entries sit scattered through every user’s run.
Good: Only leftmost prefixes seek
user_id, then created_at, then status. Never status alone.
A range before the sort column scrambles the dates
Sorted by amount, user 42’s 300 payments of ₹5,000+ must all be fetched and sorted for 20. Sorted by date, the scan stops at match 20.
Order the columns: equality, sort, range
- Tag columns: E (=), S (ORDER BY), R (range)
- Equality columns first, in any order
- Then sort columns, matching ORDER BY direction
- Ranges last, unless one is very selective
A popular rule: put the most selective column first.
The myth
For user_id = 42 AND status = 'FAILED', both orders reach the same run. Choose the prefix your other queries reuse.
Hold every column the query needs, and skip the table
INCLUDE (amount_paise) makes the monthly spend summary an Index Only Scan: Heap Fetches 0. Fresh rows not yet vacuumed still visit the table.
5 → 2
hot queries on two composite indexes
Four screens share one index on (user_id, created_at, status); reconciliation leads with status, so it gets its own. Every index taxes every insert.
Remember
An index seeks only a leftmost prefix: order columns equality, sort, range. Share few indexes; each taxes every write.