timescaledb chunk exclusion not working: slow queries scanning every chunk

TL;DR

Chunk exclusion only kicks in when the planner can prove which chunks a query needs, and several quiet things break that proof: functions wrapped around the time column, generic plans from prepared statements, constraint_exclusion turned off, or timestamp type mismatches. Run EXPLAIN, count the chunk scans, and fix whichever of those is blocking the prune. The query does not need to be rewritten, just unblocked.

EXPLAIN (COSTS OFF) shows: Append with a Seq Scan on _hyper_1_1_chunk, a Seq Scan on _hyper_1_2_chunk, and so on (every chunk scanned for a 1-day filter)

Steps

1. Run EXPLAIN (COSTS OFF) on the slow query and count the chunk scans under the Append node. Expected: every chunk is listed even though the time filter covers a narrow range. That confirms exclusion is not happening.

2. Inspect the WHERE clause. If the time column is wrapped in a function like date_trunc or a timezone conversion, rewrite the predicate to compare the raw column against literal bounds. Expected: EXPLAIN now shows only the chunks the range touches.

3. Check SHOW constraint_exclusion. It should report partition. If it is off, turn it on for the session or the database. Expected: the planner starts pruning chunks on the next EXPLAIN.

4. Check for prepared statements or a generic plan. A generic plan is built without the parameter values, so it cannot prune by them. Force custom plans or inline the literals for the test. Expected: with a custom plan the chunk list shrinks to the matching ones.

5. Check type matches. Comparing a timestamptz column against timestamp literals (or the reverse) can silently block pruning. Cast the literal to the column type. Expected: exclusion works again and the Append lists only relevant chunks.

Use this when

• A time-filtered TimescaleDB query is slow and EXPLAIN scans every chunk.
• The query was fast before and got slow after a seemingly innocent rewrite.
• You use prepared statements and the same SQL is fast with literals.
• The time predicate involves functions, casts, or timezone conversions.

Not for this skill when

• EXPLAIN already prunes to the right chunks but the query is still slow. That is an indexing or chunk-size problem inside the chunks.
• The query has no time predicate at all. Without a time filter there is nothing to exclude on.
• Chunks are enormous. Pruning works but each chunk scan is still heavy; consider smaller chunk intervals.

Variant phrasings

• timescaledb not pruning chunks
• timescale hypertable scans all chunks slow
• constraint_exclusion timescaledb not working
• timescaledb prepared statement chunk exclusion
• timescaledb date_trunc prevents chunk pruning

Why it happens

TimescaleDB stores a hypertable as many chunks, each with constraint metadata describing its time range. At plan time PostgreSQL can skip chunks whose constraints contradict the query's WHERE clause, but only when the clause is a plain comparison on the partitioning column with a knowable constant. Wrap the column in a function and the planner can no longer match it against chunk constraints; use a generic plan and the constant is not known at plan time; turn off constraint_exclusion and the whole mechanism is disabled. The data layout is fine, the planner is just blind.

Edge cases

• Space partitioning (on a second column) has the same rules: functions on the space column block its pruning too.
• Mixed timezones are a classic trap: the literal looks right but the implicit cast changes the comparison just enough to defeat the constraint match.
• Compressed chunks cannot be pruned by the same path in older versions; check your version's behavior if old chunks are always scanned.
• After fixing, verify with EXPLAIN ANALYZE that the pruned chunks are actually skipped at execution, not just hidden in the plan.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstmqyStVSQaIjC9i2oMHq1Q