---
title: "14,000x Faster Planning for LIMIT Queries on Hypertables"
published: 2026-08-27T10:18:10.000-04:00
updated: 2026-08-27T10:18:10.000-04:00
excerpt: "TimescaleDB's DeferredChunkAppend keeps planning time constant for ORDER BY LIMIT queries, no matter how many chunks a hypertable has."
tags: TimescaleDB, Engineering
authors: Sven Klemm
---

> **TimescaleDB is now Tiger Data.**

A very common query against a time-series table is [fetching the latest row for a device](https://www.tigerdata.com/blog/select-the-most-recent-record-of-many-items-with-postgresql):

```sql
SELECT * FROM metrics WHERE device = 'D001' ORDER BY time DESC LIMIT 1;
```

This should be instant. The row lives in the newest chunk, that chunk has an index on `device`, `time`, and reading it is one index scan. But on a hypertable with a few thousand chunks the query can take tens or hundreds of milliseconds before it returns anything, and nearly all of that time is spent planning rather than executing. It also gets worse as the table grows: planning time scales linearly with the number of chunks, even though the query still only reads a single row.

Across the Tiger Cloud fleet, we spend over 1,000 CPU hours every day [planning](https://www.tigerdata.com/blog/slow-query-planning-or-execution-problem) `ORDER BY time LIMIT`\-style queries.

## Where the planning time goes

A hypertable is a PostgreSQL table, and so is each of its chunks. The chunks are child tables of the hypertable, connected through PostgreSQL's table inheritance. As part of planning a query TimescaleDB expands the hypertable into its chunks and builds an append node over their scans.

TimescaleDB [prunes chunks before it expands them](https://www.tigerdata.com/blog/implementing-constraint-exclusion-for-faster-query-performance). It uses the dimension metadata in its catalog to determine which chunks the query's `WHERE` clause can touch, and only those chunks get expanded. A query like `WHERE time > now() - interval '1 hour'` only expands the chunks matching the constraint.

The cost appears when nothing prunes, which is whenever the query has no constraint on a dimension column. For every chunk that survives pruning, TimescaleDB opens and locks it, reads its statistics, and plans how to scan it. This is paid once per surviving chunk, so at 10,000 surviving chunks it dominates planning.

Our example query, `WHERE device = 'D001' ORDER BY time DESC LIMIT 1`, restricts nothing on the time dimension, so nothing prunes and every chunk survives. The `LIMIT` only bounds how many rows come back.

## Why every chunk ends up in the plan

The `LIMIT 1` does not let the planner keep only the newest chunk and drop the rest. The planner cannot prove the others are unnecessary.

A chunk can only be left out of the plan if the query's constraints show it can't contribute a row. A `LIMIT` only bounds how many rows the query returns. Nothing about it guarantees the newest chunk holds the answer. That chunk might be empty, or every row in it might be filtered out by a constraint on a non-dimension column, in which case the matching row is in an older chunk. A correct plan has to be able to fall through to those chunks.

The best available plan is an ordered append over the chunks, producing rows in time order. TimescaleDB's [ChunkAppend](https://www.tigerdata.com/blog/ordered-append-postgresql-optimization) does this: it visits chunks newest-first and stops as soon as the `LIMIT` is satisfied. For `ORDER BY time DESC LIMIT 1` it scans only one chunk, and the others show up as never executed in `EXPLAIN ANALYZE`.

The plan contains every chunk. Only plan-time pruning removes chunks from the plan, and it needs a dimension constraint the planner can fold to a constant while planning. The `LIMIT`'s early stop and ChunkAppend's run-time exclusion run at execution, so they skip scanning a chunk but do not remove it from the plan. Run-time exclusion covers constraints whose value is known only at execution, such as a query parameter or a [stable expression](https://www.postgresql.org/docs/current/xfunc-volatility.html).

When the query starts, the executor sets up each chunk's part of the plan, including those that are never executed. The chunk count is paid twice, once during planning and once during executor startup; the `LIMIT` avoids only the scanning.

## Leaving the hypertable unexpanded

`DeferredChunkAppend` is a new custom scan node that skips expansion during planning. It leaves the hypertable as a single relation instead of turning it into an append over chunks. The plan for the query above is just:

```sql
Limit
  -> Custom Scan (DeferredChunkAppend) on metrics
       Order: "time" DESC
```

No append, no per-chunk paths, nothing in the plan that grows with the number of chunks. Planning is constant.

The chunk work moves to execution instead, where the `Limit` can stop it early.

## What happens at execution time

The node fetches chunks one at a time, in the order the query requires. With `ORDER BY` on the time dimension it takes them in time order, ascending or descending, so a small `LIMIT` is usually satisfied by the first chunk. Without `ORDER BY` it takes chunks newest-first. Because it fetches lazily and stops once the `LIMIT` is satisfied, it touches only the first few chunks. `EXPLAIN (ANALYZE)` reports a `Chunks Visited` counter with the number of chunks it opened.

For each chunk the node builds a plain SQL query against the chunk's table and runs it. With a pushed-down limit it looks like this:

```sql
SELECT time, device, value FROM _timescaledb_internal._hyper_1_42_chunk
WHERE <pushed-down filter>
ORDER BY time
LIMIT 5;
```

Each chunk is scanned by a normal planned query, so the node needs no logic of its own for the different chunk states, such as uncompressed, compressed, partially compressed, or ordered; the per-chunk query handles each.

## When it applies

`DeferredChunkAppend` works by stopping once the `LIMIT` is satisfied, so it is used only when the `LIMIT` sits directly on top of the hypertable scan. An aggregate, `GROUP BY, DISTINCT`, a window function, `HAVING`, a set operation, or a join would sit between the `LIMIT` and the chunk scans, so the `LIMIT` would no longer bound how many rows are read from the chunks; those queries keep the append plan. The query must also read a single hypertable and must not have row-level-security policies, since scanning chunks directly would bypass them.

`ORDER BY` is only supported on the primary dimension in its natural order, with trailing keys allowed, because that's the order the chunk walk can produce without sorting. Ordered mode also requires a hypertable with a single (time) dimension; on a space-partitioned hypertable an `ORDER BY` query keeps the append plan, while a plain `LIMIT` with no ordering still uses the node.

The query can optionally have a `WHERE` clause, as long as it doesn't constrain a dimension column. Constraints on the dimension columns are what [ordinary chunk exclusion](https://www.tigerdata.com/docs/build/performance-optimization/improve-hypertable-performance) is for, so those queries keep the append plan. Filters on regular columns are supported and pushed into each per-chunk query.

## Numbers

The tables below show how planning time grows with the chunk count. The same data set is built two ways, as a native PostgreSQL declarative-partitioned table and as a TimescaleDB hypertable, at 1, 10, 100, 1,000, and 10,000 chunks, and `SELECT * FROM t ORDER BY time LIMIT 1` is run on PostgreSQL 17.7 and 18.3, using release builds of TimescaleDB. Each number is the median planning time over 15 warm runs. The hypertable is measured with `DeferredChunkAppend` off and on, so the two hypertable rows differ only in the feature.

**PostgreSQL 18 planning time (ms)**

| Configuration / Chunks | 1 | 10 | 100 | 1,000 | 10,000 |
| --- | --- | --- | --- | --- | --- |
| Declarative partitioning | 0.042 | 0.170 | 1.310 | 14.3 | 183 |
| Hypertable (ChunkAppend) | 0.054 | 0.190 | 1.300 | 16.3 | 202 |
| Hypertable (DeferredChunkAppend) | 0.018 | 0.017 | 0.014 | 0.014 | 0.013 |

**PostgreSQL 17 planning time (ms)**

| Configuration / Chunks | 1 | 10 | 100 | 1,000 | 10,000 |
| --- | --- | --- | --- | --- | --- |
| Declarative partitioning | 0.038 | 0.140 | 1.380 | 24.2 | 3583 |
| Hypertable (ChunkAppend) | 0.048 | 0.210 | 1.270 | 25.5 | 3660 |
| Hypertable (DeferredChunkAppend) | 0.016 | 0.018 | 0.013 | 0.013 | 0.013 |

Both plans that expand chunks, native partitioning and the hypertable with the feature off, grow with the chunk count. At 10,000 chunks on PG18 that is 183 ms for native partitioning and 202 ms for the hypertable; on PG17 native partitioning and the hypertable reach 3.6 seconds. `DeferredChunkAppend` stays around 0.014 ms across the whole range, on both versions.

Execution scales too, because the executor sets up each chunk before the append runs. The append plan's execution grows from 0.009 ms at one chunk to 27 ms at 10,000 on PG18, while `DeferredChunkAppend` stays around 0.12 ms. Execution is the smaller cost: at 10,000 chunks the append plan spends about 27 ms executing against 200 ms planning on PG18.

This continues work we described in [Optimizing queries on TimescaleDB hypertables with thousands of partitions](https://www.tigerdata.com/blog/optimizing-queries-timescaledb-hypertables-with-partitions-postgresql-6366873a995d), which cut planning time 15x by speeding up chunk expansion itself. `DeferredChunkAppend` takes the next step for this query shape: instead of expanding faster, it skips expansion entirely.

## Trying it

`DeferredChunkAppend` ships in TimescaleDB 2.30. It's on by default, behind a GUC:

```sql
SET timescaledb.enable_deferred_chunk_append TO on; – default
```

Run a qualifying LIMIT query and look at the plan:

```sql
EXPLAIN (COSTS OFF) SELECT * FROM metrics ORDER BY time DESC LIMIT 1;
EXPLAIN (COSTS OFF) SELECT * FROM metrics WHERE device='D0001' ORDER BY time DESC LIMIT 1;
```

The plan should show a `Custom Scan` (`DeferredChunkAppend`) node in place of an append over the chunks.

## Summary

Expanding a hypertable into all its chunks makes sense when a query reads most of the data. A `LIMIT` query that returns only a few rows doesn't; it was paying that same per-chunk cost for every chunk without needing it. A `LIMIT` bounds what comes back, not what gets planned; those are separate costs, and mixing them up is what made this query slow in the first place. `DeferredChunkAppend` is what happens when you stop conflating them: it defers the chunk work to execution, where the `LIMIT` ends it early, so fetching the latest row takes constant planning time regardless of the number of chunks.

`DeferredChunkAppend` is already running across the Tiger Cloud fleet, on by default. If you want to see it on your own hypertables, [create a Tiger Cloud account](https://console.cloud.timescale.com/signup) and run `EXPLAIN` on your own `ORDER BY time LIMIT` queries.