Skip to content

Why is a range query on created_at slow even with an index?

Last updated

View as Markdown

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 BY the index cannot supply, a sort spilling to disk, a function around the column, and OFFSET pagination.

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.

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.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, customer_id, total
FROM orders
WHERE 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 Scan and Seq Scan fail for different reasons and have different fixes.
  • Estimated rows= against actual 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.
  • Buffers on the node that spent the time, not on the top of the plan. Upper nodes include their children, so a large shared read at the root says nothing about where the reads happened.
  • Whether a Sort appears, and its Sort Method. external merge with temp read= and written= means the sort went to disk, which is a work_mem problem 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, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';

Same table, same index, four widths and one sort. Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container:

what was askedplanrowstime
one hour of rowsIndex Scan5710.243 ms
seven daysIndex Scan96,00026.8 ms
one quarterIndex Scan1,234,286413 ms
count(*) over seven daysIndex Only Scan, Heap Fetches: 096,00020.5 ms
seven days through a BRIN indexBitmap Heap Scan, Heap Blocks: lossy=97196,00020.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 common fix is wrong here.

SELECT id, total
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-03-01 00:00:00+00'
AND created_at < TIMESTAMPTZ '2026-03-31 00:00:00+00'
ORDER BY total DESC
LIMIT 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 ms

Note 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 builtwhat the planner didtime
none beyond (created_at)Index Scan then top-N heapsort159 ms
(created_at, total DESC), 150 MBignored it, identical plan166 ms
(total DESC), 107 MBIndex Scan on it, stopped after 100.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:

windowRows Removed by Filter before 10 were foundtime
one month950.674 ms
one day5,19628.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 querywhat serves it
range on created_at, ordered by created_atthe 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 elseno 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.

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=1266
Execution Time: 485.835 ms

At 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=2543
Execution Time: 453.605 ms

external 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 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 windowHeap Fetchestime
never updated020.5 ms
960 rows updated, then VACUUM95,04028.5 ms
then VACUUM (INDEX_CLEANUP ON)013.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 identifiers
visibility map: 0 pages set all-visible, 0 pages set all-frozen

The 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.

Measured on 2026-09-20 on that PostgreSQL 18.6 dbg-pgrange container, with the created_at index present:

SELECT count(*) FROM orders
WHERE 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: 4986286
Execution Time: 1067.283 ms

On 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 orders
WHERE 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 IMMUTABLE

date_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.

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 dayits actual length
2026-03-08, spring forward23:00:00
2026-11-01, fall back1 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-05result
+ 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 orders
WHERE 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.

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 forindex rows readbufferstime
OFFSET 50000 LIMIT 2050,0201959.82 ms
WHERE (created_at, id) < ($1, $2) LIMIT 202040.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_at
FROM orders
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 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: 425143

Estimated 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 IN clauses 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, LIKE or any other type of condition.

Extended Statistics, PostgreSQL 18 manual

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.

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.

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.