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

## 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

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?

<!-- sql-check: skip needs the 5,000,000 row bench table -->
```sql
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:

<!-- sql-check: postgres -->
```sql
SELECT attname, correlation
FROM pg_stats
WHERE tablename = 'orders' AND attname = 'created_at';
```

## 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

### The `ORDER BY` the index cannot supply

The common fix is wrong here.

<!-- sql-check: skip needs the 5,000,000 row bench table -->
```sql
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 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

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

<!-- sql-check: postgres -->
```sql
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

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

<!-- sql-check: skip needs the 5,000,000 row bench table -->
```sql
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:

<!-- sql-check: postgres -->
```sql
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:

<!-- sql-check: expect-error 42P17 -->
```sql
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:

<!-- sql-check: postgres -->
```sql
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

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:

<!-- sql-check: postgres -->
```sql
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.

### 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:

<!-- sql-check: postgres -->
```sql
CREATE INDEX idx_orders_created_id_desc ON orders (created_at DESC, id DESC);
```

<!-- sql-check: skip a parameterised cursor needs bind values -->
```sql
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

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](https://www.postgresql.org/docs/18/planner-stats.html)

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?

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?

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:

<!-- sql-check: postgres -->
```sql
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.

## How DBGorilla helps

DBGorilla connects read-only and gives your AI coding agent the live plan for
the query plus the per-table sequential and index scan counts, the index sizes,
and how many rows the table actually holds, so it can say whether the range is
genuinely wide or the predicate is stopping the index being used. All of that
was verified in a live session.
[Get started free →](https://app.dbgorilla.com/signup)

## Related

- [Why Postgres uses a sequential scan despite an index](https://www.dbgorilla.com/learn/postgres/why-is-postgres-using-a-sequential-scan-when-i-have-an-index/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- [Which PostgreSQL index type should I use?](https://www.dbgorilla.com/learn/postgres/which-postgresql-index-type-should-i-use/)