Range Conditions and Index Cond Boundaries #

An index on (created_at, account_id) serves WHERE created_at >= now() - interval '30 days' AND account_id = 42. The plan shows both conditions under Index Cond, which looks ideal. The scan reads 180,000 index pages to return 1,200 rows. Swapping the index to (account_id, created_at) reads 14 pages. Both plans list the same Index Cond; only one of them uses both conditions to limit where the scan starts and stops.

Column ordering rules in general are covered in multicolumn index column ordering. This page covers the specific way EXPLAIN hides the difference between a condition that bounds a scan and one that is merely checked inside it.

The Index Condition #

A B-tree scan is defined by a start key and a stop condition in index order. The planner derives them from the index conditions on a prefix of the index columns:

EXPLAIN prints all such conditions together as Index Cond. It does not say which ones bound the scan. The evidence is in Buffers and, from PostgreSQL 18, in Index Searches.

So for (created_at, account_id) with a 30-day range on created_at, the scan visits every entry in the 30 days — all accounts — and checks account_id = 42 on each. For (account_id, created_at), equality on account_id fixes the prefix and the range on created_at bounds it: the scan touches only account 42’s last 30 days.

PostgreSQL 18’s skip scan can partially rescue the first ordering when the range column has few distinct values, but a continuous timestamp range has too many for that to help, as explained in B-tree skip scan for omitted leading columns.

Same conditions, different scan ranges Left, index on created_at then account_id: the range on created_at defines the scan, covering every account for 30 days, and account_id is checked entry by entry. Right, index on account_id then created_at: equality on account_id fixes the prefix and the range bounds it, so only account 42's recent entries are read. INDEX (created_at, account_id) start/stop from created_at range account_id = 42 checked per entry reads all accounts' 30 days ≈ 180,000 index pages INDEX (account_id, created_at) start/stop from account_id + range both conditions bound the scan reads one account's 30 days ≈ 14 index pages equality columns first, then the range column

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, amount FROM ledger
WHERE created_at >= now() - interval '30 days' AND account_id = 42;

With INDEX (created_at, account_id):

Index Scan using ledger_created_account_idx on ledger
    (cost=0.57..402108.40 rows=1210 width=16) (actual time=0.04..2804.6 rows=1204 loops=1)
  Index Cond: ((created_at >= (now() - '30 days'::interval)) AND (account_id = 42))
  Index Searches: 1
  Buffers: shared hit=12040 read=168220
Execution Time: 2805.1 ms
-- 180k buffers for 1,204 rows: account_id was checked inside a 30-day range, not used to bound it

With INDEX (account_id, created_at):

Index Scan using ledger_account_created_idx on ledger
    (cost=0.57..1204.40 rows=1210 width=16) (actual time=0.03..1.9 rows=1204 loops=1)
  Index Cond: ((account_id = 42) AND (created_at >= (now() - '30 days'::interval)))
  Index Searches: 1
  Buffers: shared hit=14
Execution Time: 2.1 ms
-- identical Index Cond text; 14 buffers: both conditions bound the scan
Same Index Cond, different work Three items are annotated. Both plans print the same two conditions under Index Cond. Buffers of 180 thousand for 1,204 rows show conditions checked across a wide range. Buffers of 14 for the same rows show both conditions bounding the scan. Index Cond: (created_at >= … AND account_id = 42) printed the same in both plans Buffers: shared hit=12040 read=168220 wide range, entries checked Buffers: shared hit=14 tight range, conditions bound it judge an index scan by buffers per row, not by the Index Cond text

Step-by-Step Resolution #

  1. Compute index buffers per returned row. Tens or hundreds of buffers per row on an index scan whose conditions all appear in Index Cond suggest a non-bounding condition.

  2. Identify the first range column in the index’s column order. Conditions on columns after it are checked, not bounding.

  3. Reorder: equality columns first, then one range column, then columns needed only for filtering or covering:

    CREATE INDEX CONCURRENTLY ledger_account_created_idx ON ledger (account_id, created_at);

  4. Keep the old index only if other queries need its order. A pure time-range query without account_id still wants created_at first; check pg_stat_user_indexes and query patterns before dropping it — see finding unused indexes safely.

  5. With two range conditions, only one can bound the scan. Put the more selective range first, or convert one into equality where the domain allows (for example day = ANY (…) for a short list of dates).

  6. Verify with buffers and timing, not with the Index Cond text.

Pages read for 1,204 rows With created_at first, the scan reads 180,260 index and heap pages. With account_id first it reads 14. Rewriting a two-range query as equality on a list of days with a day-first index reads 96 pages. (created_at, account_id) 180,260 buffers (account_id, created_at) 14 buffers (day, account_id) + IN list 96 buffers equality before range keeps both conditions bounding

Before and After #

-- BEFORE: INDEX (created_at, account_id)
Index Cond: ((created_at >= …) AND (account_id = 42))   Buffers: shared hit=12040 read=168220   Execution Time: 2805.1 ms

-- AFTER: INDEX (account_id, created_at)
Index Cond: ((account_id = 42) AND (created_at >= …))   Buffers: shared hit=14                   Execution Time: 2.1 ms

IN lists and bounded scans #

= ANY conditions on a leading column are handled as a series of equality searches. An index on (status, created_at) serving status IN ('queued', 'retry') AND created_at >= $1 performs one bounded search per status value, each narrowed by the range — which Index Searches: 2 shows in PostgreSQL 18. That makes low-cardinality IN lists a useful way to keep an equality-first index when the query naturally has a small set of values rather than one. Very long lists lose the benefit, because each value costs a descent; hundreds of values over a range is often cheaper as a bitmap scan or with the range column first.

The same reasoning explains a common surprise with ORDER BY: an index on (account_id, created_at) provides created_at order only within a single account. A query for several accounts ordered by created_at gets several bounded searches but must merge or sort their results, while an index on created_at provides global order but cannot bound by account. The trade-off between filtering and ordering is covered in covering indexes for ORDER BY and LIMIT.

Common Pitfalls #

Trusting the Index Cond text. Bounding and checked conditions print identically. Diagnostic signal: high buffers per row. Fix: reason from column order.

Timestamp first by habit. Time-series tables often get (created_at, …) indexes. Diagnostic signal: per-entity recent queries reading whole time ranges. Fix: entity column first for per-entity access.

Two ranges in one index. Only the first bounds the scan. Diagnostic signal: second range checked across the first’s whole span. Fix: most selective range first, or a GiST index for true multi-dimensional ranges.

Adding columns after a range for selectivity. They reduce heap fetches but not index reads. Diagnostic signal: low heap buffers, high index buffers. Fix: move equality columns before the range column.

Frequently Asked Questions #

Why does my index scan read so many pages when all conditions are in Index Cond? #

EXPLAIN lists every condition evaluated inside the index as Index Cond, but only equality conditions on leading columns plus the first range condition bound where the scan starts and stops. Conditions on columns after a range are checked on each entry within that range.

What is the right column order for equality and range conditions? #

Put columns compared with equality first and the column with a range condition after them. That lets every condition narrow the part of the index the scan reads.

Does PostgreSQL 18 skip scan fix a range on the first column? #

Only when the leading column has few distinct values within the range. A continuous timestamp range has too many distinct values for skip scan to help; reordering the index is the reliable fix.

Up: B-Tree Index Optimization