Why is a range query on created_at slow even with an index?
Quick answer: Read the plan before blaming the heap. A wide range has a cost, but on a table whose rows arrive in time order the heap access is not scattered at all. Common causes are an
ORDER BYthe index cannot supply, a sort spilling to disk, a function around the column, andOFFSETpagination.
What is the problem?
Section titled “What is the problem?”An index on created_at lets PostgreSQL jump to the start of your range and walk
forward, so the index work scales with the size of the range rather than the size
of the table. What happens next depends on the plan, and there are at least five
different plans PostgreSQL might choose.
The useful question is which plan you got and which node spent the time. Whether the index is being used tells you little.
The bench
Section titled “The bench”Everything below was measured on 2026-09-20 on PostgreSQL 18.6, image
postgres:18, in a container named dbg-pgrange on a host with 8 vCPUs and
62 GB of RAM running Linux 6.17.0-1022-azure under Docker 29.1.3, at the image’s
default server settings.
Loaded into that PostgreSQL 18.6 dbg-pgrange container on 2026-09-20, the
table is 5,000,000 orders spread evenly over one year, one row every 6.3
seconds, 326 MB of heap with a 107 MB B-tree on created_at. Rows were inserted
in time order and not updated afterwards. This ordering is the main condition
behind the results below. Parallel query was switched off for every plan below
so the numbers are one worker’s work.
The insert order cuts both ways. This bench’s pg_stats.correlation for
created_at is 0.9997. If your table
is heavily updated or your rows arrive out of order, the heap numbers here will
not reproduce, and scattered heap reads may be the issue. The way to find out is
the query at the end of this section.
How do I confirm it?
Section titled “How do I confirm it?”EXPLAIN (ANALYZE, BUFFERS)SELECT id, customer_id, totalFROM ordersWHERE created_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00' AND created_at < TIMESTAMPTZ '2026-03-08 00:00:00+00';Then read four things in this order.
- The scan node’s name.
Index Scan,Bitmap Heap Scan,Index Only ScanandSeq Scanfail for different reasons and have different fixes. - Estimated
rows=againstactual rows=, on every node. A large divergence means the planner chose on bad information, and adding an index to a plan built on a wrong estimate usually does nothing. Bufferson the node that spent the time, not on the top of the plan. Upper nodes include their children, so a largeshared readat the root says nothing about where the reads happened.- Whether a
Sortappears, and itsSort Method.external mergewithtemp read=andwritten=means the sort went to disk, which is awork_memproblem rather than an index problem.
Then check the correlation, because it determines whether the heap access in your plans is sequential or scattered:
SELECT attname, correlationFROM pg_statsWHERE tablename = 'orders' AND attname = 'created_at';The five plans, measured
Section titled “The five plans, measured”Same table, same index, four widths and one sort. Measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container:
| what was asked | plan | rows | time |
|---|---|---|---|
| one hour of rows | Index Scan | 571 | 0.243 ms |
| seven days | Index Scan | 96,000 | 26.8 ms |
| one quarter | Index Scan | 1,234,286 | 413 ms |
count(*) over seven days | Index Only Scan, Heap Fetches: 0 | 96,000 | 20.5 ms |
| seven days through a BRIN index | Bitmap Heap Scan, Heap Blocks: lossy=971 | 96,000 | 20.7 ms |
The third row shows the main point. A quarter is 25 percent of this table, and
the usual advice says a range that wide should become a sequential scan with
scattered heap reads. Measured on 2026-09-20 on that PostgreSQL 18.6
dbg-pgrange container, it stayed on the index and read 12,395 blocks from
disk in 413 ms, because at a correlation of 0.9997 walking the index in key
order walks the heap in physical order. Those reads are sequential.
A wide range makes the index useless only when the heap order and the column disagree.
The costs that are actually there
Section titled “The costs that are actually there”The ORDER BY the index cannot supply
Section titled “The ORDER BY the index cannot supply”The common fix is wrong here.
SELECT id, totalFROM ordersWHERE created_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00' AND created_at < TIMESTAMPTZ '2026-03-31 00:00:00+00'ORDER BY total DESCLIMIT 10;Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, with only
the created_at index present:
Limit (actual time=159.486..159.490 rows=10.00 loops=1) -> Sort (actual time=159.484..159.486 rows=10.00 loops=1) Sort Key: total DESC Sort Method: top-N heapsort Memory: 25kB -> Index Scan using idx_orders_created on orders (actual rows=411428.00 loops=1)Execution Time: 159.522 msNote the top-N heapsort and, on that 2026-09-20 PostgreSQL 18.6 dbg-pgrange
run, the 25 kB. The LIMIT is not useless: it turned a sort of 411,428
rows into a bounded one that keeps ten. It cannot stop reading, because nothing
can know the largest total in a month without looking at the month.
The index usually recommended for this does not work. Measured on 2026-09-20
on that PostgreSQL 18.6 dbg-pgrange container:
| index built | what the planner did | time |
|---|---|---|
none beyond (created_at) | Index Scan then top-N heapsort | 159 ms |
(created_at, total DESC), 150 MB | ignored it, identical plan | 166 ms |
(total DESC), 107 MB | Index Scan on it, stopped after 10 | 0.674 ms |
On that 2026-09-20 PostgreSQL 18.6 dbg-pgrange run the 150 MB index changed
nothing. A B-tree on (created_at, total DESC) orders rows by total
within each identical created_at, and a range predicate leaves 411,428
distinct timestamps in play, so it cannot emit the month’s rows in global
total order. The planner was right to ignore it.
The index that worked leads with the sort column. It walks total from the top
and stops when ten rows fall inside the window. Measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container it discarded 95 rows to find 10.
How early it stops depends on the window width. Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container:
| window | Rows Removed by Filter before 10 were found | time |
|---|---|---|
| one month | 95 | 0.674 ms |
| one day | 5,196 | 28.9 ms |
Fifty-five times as many rows discarded, and forty-three times the time, for a
window thirty times narrower. Push it further and the total DESC index becomes
a scan of the whole index.
The rule, in three cases, each measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container:
| the query | what serves it |
|---|---|
range on created_at, ordered by created_at | the plain (created_at) index, read backwards. Measured at 0.048 ms for LIMIT 20 |
| equality on a column, ordered by another | (that column, the sort column). Measured at 0.111 ms for (customer_id, total DESC) |
range on created_at, ordered by something else | no single B-tree does both. Choose which end to optimise, or pre-aggregate |
Putting the range column first and the sort column second applies the middle
row’s rule to the bottom row’s problem. It is wrong for a range, and on that 2026-09-20 PostgreSQL 18.6 dbg-pgrange run the
150 MB of index it builds is wasted.
The sort that went to disk
Section titled “The sort that went to disk”Drop the LIMIT from that same query and the bounded sort becomes a real one.
It does not need a lowered work_mem to go to disk. At the container’s default
4 MB, measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container:
Sort (actual time=365.618..463.612 rows=411428.00 loops=1) Sort Key: total DESC Sort Method: external merge Disk: 10088kB Buffers: shared hit=2578 read=3741, temp read=1261 written=1266Execution Time: 485.835 msAt 1 MB on that same PostgreSQL 18.6 dbg-pgrange container on 2026-09-20,
the same statement:
Sort (actual time=349.499..431.998 rows=411428.00 loops=1) Sort Key: total DESC Sort Method: external merge Disk: 10088kB Buffers: shared hit=6319, temp read=2520 written=2543Execution Time: 453.605 msexternal merge in both, and temp traffic in both Buffers lines. On that
PostgreSQL 18.6 dbg-pgrange container on 2026-09-20 the 411,428 rows did not
fit in 4 MB, so the default server is the spilling case rather than the safe
one, and lowering the setting made the temp traffic larger without changing the
method or the 10088kB on disk.
The elapsed times do not show the 1 MB run winning. The 4 MB run was
measured cold and the 1 MB run warm, on that PostgreSQL 18.6 dbg-pgrange
container on 2026-09-20, and each plan’s own Buffers line records it: 3,741
blocks read from disk in the first against none in the second, the same blocks
already in shared buffers by then. Those two elapsed times are not a comparison.
The comparable numbers are the temp counts, which went up when the memory went
down.
No index fixes this. Either return fewer rows, or give the sort more memory.
An index-only scan is not a promise
Section titled “An index-only scan is not a promise”An index-only scan avoids reading the heap tuple, but only for pages the
visibility map marks all-visible. Otherwise it visits the heap anyway, under a
plan node that still says Index Only Scan.
Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, on a
covering index over a 96,000 row window:
| state of the window | Heap Fetches | time |
|---|---|---|
| never updated | 0 | 20.5 ms |
960 rows updated, then VACUUM | 95,040 | 28.5 ms |
then VACUUM (INDEX_CLEANUP ON) | 0 | 13.2 ms |
On that 2026-09-20 PostgreSQL 18.6 dbg-pgrange run, updating one percent of
the window cost 99 percent of its rows a heap fetch, and an ordinary VACUUM
did not fix it. It said why:
index scan bypassed: 801 pages from table (1.92% of total) have 960 dead item identifiersvisibility map: 0 pages set all-visible, 0 pages set all-frozenThe 960 updated rows were spread thinly over 801 pages of the window, one or
two to a page, so each of those pages lost its all-visible bit. VACUUM skips
the index scan while the pages holding dead line pointers are under 2 percent of
the table’s pages, and these 801 pages were 1.92 percent. Forcing that index scan set all
801 pages all-visible and halved the query.
On a table with even a trickle of updates, an index-only scan is a plan shape
you have to verify with Heap Fetches rather than a property you can design
for.
CREATE INDEX idx_orders_created_incl ON orders (created_at) INCLUDE (id, total);On that 2026-09-20 PostgreSQL 18.6 dbg-pgrange container that index cost
193 MB against the 107 MB of the plain one, so it is a real trade even when it
works.
The timestamp wrapped in a function
Section titled “The timestamp wrapped in a function”Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, with the
created_at index present:
SELECT count(*) FROM ordersWHERE date_trunc('day', created_at) = TIMESTAMPTZ '2026-03-01 00:00:00+00';Seq Scan on orders (actual time=506.709..1066.348 rows=13714.00 loops=1) Filter: (date_trunc('day'::text, created_at) = '2026-03-01 00:00:00+00'::timestamp with time zone) Rows Removed by Filter: 4986286Execution Time: 1067.283 msOn that 2026-09-20 PostgreSQL 18.6 dbg-pgrange run, 1,067 ms to find 13,714
rows. The half-open range on the bare column takes the index:
SELECT count(*) FROM ordersWHERE created_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00' AND created_at < TIMESTAMPTZ '2026-03-02 00:00:00+00';Calling it unindexable would be too strong, though. An expression index can serve an expression predicate. For a timestamptz column
there is a catch, measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange
container:
CREATE INDEX idx_orders_day ON orders ((date_trunc('day', created_at)));ERROR: functions in index expression must be marked IMMUTABLEdate_trunc on a timestamptz depends on the session’s TimeZone, so it is
not immutable and cannot be indexed. Pin the zone inside the expression and it
can:
CREATE INDEX idx_orders_day_utc ON orders ((date_trunc('day', created_at AT TIME ZONE 'UTC')));Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, the
matching query then ran in 2.80 ms against the sequential scan’s 1,067 ms. Wrap
the column only if you are prepared to index that exact expression, in that
exact zone, and pay for it on every write. A half-open range on the bare column
needs no second index.
Calendar days are not 24 hours
Section titled “Calendar days are not 24 hours”A half-open range is only a convention. The time zone decides whether its boundaries are right.
Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container with
TimeZone set to America/New_York:
| the local civil day | its actual length |
|---|---|
| 2026-03-08, spring forward | 23:00:00 |
| 2026-11-01, fall back | 1 day 01:00:00 |
So deriving the upper bound by adding hours is wrong on two days a year in every
DST-observing zone. Adding interval '1 day' to a timestamptz lands on the
next local midnight; adding interval '24 hours' lands an hour past it:
| expression, from 2026-03-08 00:00:00-05 | result |
|---|---|
+ interval '1 day' | 2026-03-09 00:00:00-04 |
+ interval '24 hours' | 2026-03-09 01:00:00-04 |
Compute both boundaries as civil-time boundaries in the zone the business means, then compare the bare column:
SET TimeZone = 'America/New_York';SELECT count(*) FROM ordersWHERE created_at >= DATE '2026-03-08'::timestamptz AND created_at < (DATE '2026-03-08' + interval '1 day')::timestamptz;Picking UTC is a decision in its own right. A customer-facing “March 8” usually means a local civil day.
Use >= with < rather than BETWEEN, which is inclusive at both ends.
Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, on three
rows of which one sat exactly on midnight: BETWEEN returned 2 and the half-open
range returned 1.
Pagination by OFFSET
Section titled “Pagination by OFFSET”OFFSET 50000 does not skip rows for free. Measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container, on a 100,000 row table with a matching
index:
| how the page was asked for | index rows read | buffers | time |
|---|---|---|---|
OFFSET 50000 LIMIT 20 | 50,020 | 195 | 9.82 ms |
WHERE (created_at, id) < ($1, $2) LIMIT 20 | 20 | 4 | 0.052 ms |
Those two rows come from a second bench table built on 2026-09-20 in that same
PostgreSQL 18.6 dbg-pgrange container, dup: 100,000 rows in which 50 rows
share each timestamp, which is what a created_at
with second resolution looks like under load, and the duplicates are what this
test depends on.
The cursor has to carry the whole ordering key, not just the timestamp. A cursor
carrying only created_at while ordering by created_at DESC, id DESC silently
loses rows. Measured on that
dup table on 2026-09-20 in that PostgreSQL 18.6 dbg-pgrange container,
resuming from the last row of page one by timestamp alone skipped 31 rows
that belonged on page two.
The row comparison fixes it, and PostgreSQL can turn it into an index condition:
CREATE INDEX idx_orders_created_id_desc ON orders (created_at DESC, id DESC);SELECT id, created_atFROM ordersWHERE (created_at, id) < ($1::timestamptz, $2::bigint)ORDER BY created_at DESC, id DESCLIMIT 20;The same shape against the dup table, with the values filled in, is what the
plan above was taken from:
Index Only Scan using idx_dup on dup (actual rows=20.00 loops=1) Index Cond: (ROW(created_at, id) < ROW('2026-03-01 00:16:39+00'::timestamp with time zone, 50001))$1 and $2 both come from the last row of the previous page, at full timestamp
precision. If rows are being inserted while somebody pages, decide deliberately
whether the pages are a snapshot or a live view; keyset pagination is stable
against the key, not against the data.
The estimate was wrong before the plan was chosen
Section titled “The estimate was wrong before the plan was chosen”Check this before adding any index. Measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container, on a table where status = 'refunded'
exists only in September:
Index Scan using idx_orders_created on orders (cost=0.43..18661.51 rows=22403 width=0) (actual rows=0.00 loops=1) Index Cond: ((created_at >= '2026-08-01 00:00:00+00') AND (created_at < '2026-09-01 00:00:00+00')) Filter: (status = 'refunded'::text) Rows Removed by Filter: 425143Estimated 22,403, actual 0. The planner multiplied two selectivities as though the columns were independent, and they are perfectly dependent.
The usual remedy, extended statistics, does not help here. Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container,
creating CREATE STATISTICS ... (dependencies, mcv) ON status, created_at and
re-analyzing moved the estimate from 22,403 to 22,326. Neither kind helped, for
two different reasons.
Functional dependencies are equality-only, and the manual says so:
Functional dependencies are currently only applied when considering simple equality conditions that compare columns to constant values, and
INclauses with constant values. They are not used to improve estimates for equality conditions comparing two columns or comparing a column to an expression, nor for range clauses,LIKEor any other type of condition.
Multivariate MCV lists are not. PostgreSQL 18’s
statext_is_compatible_clause_internal accepts =, <, >, >= and <=, so
a range is exactly the kind of clause an MCV list is meant to rescue. It failed
here because there was no list to consult. created_at is unique on this table:
measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container,
5,000,000 distinct values in 5,000,000 rows, so no pairing of status and
created_at repeats often enough for ANALYZE to record one. Read on the same
container on 2026-09-20 after a fresh ANALYZE, pg_statistic_ext_data.stxdmcv
was null while stxddependencies was populated.
Extended statistics are sound in general, but on a column with no repeated values the MCV list is empty. The fix here is a partial index, a different column layout, or partitioning.
Why does AI-generated code cause this?
Section titled “Why does AI-generated code cause this?”The natural-language version of a date filter maps directly onto the
non-sargable form: “orders from March” becomes date_trunc('month', created_at)
or to_char(created_at, 'YYYY-MM'). Both read naturally, both put a function
around the indexed column, and to_char additionally turns a timestamp
comparison into a text one.
The second pattern is OFFSET pagination, which is the textbook answer to “page
through results”, is correct, and gets slower with every page. The third is the
composite index with the range column first: the right answer to a query with an
equality filter, applied to one with a range.
How do I stop it coming back?
Section titled “How do I stop it coming back?”Read the plan before changing an index. Scan node, estimate against actual, buffers on the node that spent the time, sort method. The spilled sort, the heap fetches and the block counts on this page are all invisible if you read only the total time.
Prefer half-open ranges, and derive the bounds in the business’s time zone.
>= start AND < end is the convention; the zone is what makes the two values
correct.
Make cursors carry the whole ordering key: (created_at, id) < ($1, $2),
with an index in the matching order.
Check Heap Fetches before relying on an index-only scan, and remember an
ordinary VACUUM can decline to do the cleanup that makes one possible.
Measure BRIN before dismissing it. Measured on 2026-09-20 on that
PostgreSQL 18.6 dbg-pgrange container at a correlation of 0.9997, a BRIN index
on created_at was 32 kB against the B-tree’s 107 MB, and the seven-day count
ran in 20.7 ms against the B-tree’s 20.5 ms:
CREATE INDEX idx_orders_created_brin ON orders USING brin (created_at);The bitmap it produces is lossy, so the plan rechecks: that run reported
Heap Blocks: lossy=971 and Rows Removed by Index Recheck: 19520. It is also
the index type that degrades hardest when the correlation goes, because the
summary of each block range stops being selective. Check pg_stats.correlation
first, and check it again after a bulk update.
Consider partitioning by time once most queries touch one recent slice.
Pruning happens before any index is consulted, and dropping old data becomes a
DROP TABLE rather than a DELETE. It also costs planning time on queries that
cannot prune, so it is a design decision rather than a tuning one.
Why is my created_at range query slow when the column is indexed?
Read the plan before assuming. A wide range can be slow because it genuinely matches a lot of rows, because the ORDER BY cannot be served by the index, because the sort spilled to disk, or because heap access was scattered. Measured on 2026-09-20 on PostgreSQL 18.6 in container dbg-pgrange, a quarter-wide range over 5,000,000 rows returned 1,234,286 rows in 413 ms on an ordinary index scan, and that was the correct plan.
Does an index on (created_at, total DESC) make ORDER BY total DESC LIMIT 10 fast over a date range?
No. Measured on 2026-09-20 on PostgreSQL 18.6 in container dbg-pgrange, building that 150 MB index changed nothing: the planner ignored it and kept the same 166 ms top-N heapsort. A B-tree after a range predicate orders rows within each identical created_at value, not globally. An index leading with total DESC took the same query to 0.674 ms on that same 2026-09-20 PostgreSQL 18.6 dbg-pgrange run.
Does an index-only scan avoid the heap?
Only for heap pages marked all-visible. Measured on 2026-09-20 on PostgreSQL 18.6 in container dbg-pgrange, updating 1 percent of the rows in a 96,000 row window left the plan named Index Only Scan reporting Heap Fetches: 95,040 and taking 28.5 ms, because VACUUM had bypassed index vacuuming and could not set the visibility map. After VACUUM (INDEX_CLEANUP ON) it was 0 fetches and 13.2 ms.
Should I use BRIN instead of B-tree for a created_at column?
It is worth measuring when the physical row order follows the column. Measured on 2026-09-20 on PostgreSQL 18.6 in container dbg-pgrange at a pg_stats correlation of 0.9997, a BRIN index was 32 kB against the B-tree's 107 MB, and a seven-day count took 20.7 ms against the B-tree's 20.5 ms. Same answer, same time, and the two sizes are the whole argument.
Does BETWEEN work for timestamp ranges?
It works but it is inclusive at both ends, so it picks up rows landing exactly on the upper boundary. Measured on 2026-09-20 on PostgreSQL 18.6 in container dbg-pgrange, on three rows where one sat exactly at midnight, BETWEEN returned 2 and the half-open range returned 1. Use created_at >= start AND created_at < end.