quotes is partitioned by day on ts and holds 3 years. The query WHERE ts::date BETWEEN '2026-01-02' AND '2026-01-03' scans 1,095 partitions instead of 2. Say why, give the rewrite, and state how many partitions the rewrite reads.

quotes is partitioned by day on ts and holds 3 years. The query WHERE ts::date BETWEEN '2026-01-02' AND '2026-01-03' scans 1,095 partitions instead of 2. Say why, give the rewrite, and state how many partitions the rewrite reads.

Approach: Ask which expression the partition boundaries are declared on and whether the predicate is written on that same expression, then check the inclusivity of the bounds.

The cast hides the partition key from the planner, so rewrite as ts >= '2026-01-02' AND ts < '2026-01-04' and the query reads 2 partitions. Partition pruning compares the predicate's bounds against each partition's declared bounds, and both must be expressed on the same partition key expression. ts::date is a function of ts, and the planner does not invert functions, so it cannot map a range of dates back to a range of timestamps and keeps all 1,095 partitions in the plan. BETWEEN carries a second bug once the cast is removed: ts BETWEEN '2026-01-02' AND '2026-01-03' is inclusive at both ends and therefore stops at midnight on the third, silently dropping that whole day's ticks, which is why the half open range is the form to write. The same failure appears through an implicit cast when the column is timestamptz and the literal is resolved in a session time zone that shifts the boundary, so state the zone in the literal. Verify the fix by reading the plan and counting the partitions it lists, never by timing the query, because a warm cache hides the difference.

Follow-up: How do you keep pruning when the query filters on a business date offset from the UTC date?

Key concepts: partition pruning, partition key expression, half open range, implicit cast.