A very common query against a time-series table is fetching the latest row for a device:
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 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. 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 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.
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:
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:
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 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, 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:
SET timescaledb.enable_deferred_chunk_append TO on; – default
Run a qualifying LIMIT query and look at the plan:
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 and run EXPLAIN on your own ORDER BY time LIMIT queries.
Top comments (0)