By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE foo (
id integer NOT NULL
, ts timestamp NOT NULL
, count integer DEFAULT 0
, PRIMARY KEY (ts, id) -- better!
)
PARTITION BY RANGE (ts);
CREATE TABLE foo_p1 PARTITION OF foo FOR VALUES FROM ('2023-01-01') TO ('2023-01-02');
CREATE TABLE foo_p2 PARTITION OF foo FOR VALUES FROM ('2023-01-02') TO ('2023-01-03');
CREATE TABLE foo_p3 PARTITION OF foo FOR VALUES FROM ('2023-01-03') TO ('2023-01-04');
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
SHOW enable_partition_pruning;
SET enable_seqscan = off; -- force idx scan for minimal table
| enable_partition_pruning |
|---|
| on |
SHOW
SET
-- partition pruning done during query planning
-- already visible with EXPLAIN - without executing the query
EXPLAIN -- !!!
SELECT sum(foo.count) AS total,
date_trunc('day', foo.ts) AS day_date
FROM foo
-- WHERE foo.date BETWEEN '2023-01-01' AND '2023-01-02'
WHERE foo.ts >= '2023-01-01'
AND foo.ts < '2023-01-02' -- ! exclude upper bound
GROUP BY day_date;
| QUERY PLAN |
|---|
| HashAggregate (cost=14.88..14.99 rows=9 width=16) |
| Group Key: date_trunc('day'::text, foo.ts) |
| -> Bitmap Heap Scan on foo_p1 foo (cost=4.24..14.83 rows=9 width=12) |
| Recheck Cond: ((ts >= '2023-01-01 00:00:00'::timestamp without time zone) AND (ts < '2023-01-02 00:00:00'::timestamp without time zone)) |
| -> Bitmap Index Scan on foo_p1_pkey (cost=0.00..4.24 rows=9 width=0) |
| Index Cond: ((ts >= '2023-01-01 00:00:00'::timestamp without time zone) AND (ts < '2023-01-02 00:00:00'::timestamp without time zone)) |
EXPLAIN
-- partition pruning done during execution
-- only visible with EXPLAIN (ANALYZE) - actually executing the query
EXPLAIN (ANALYZE) -- !!!
WITH times AS ( -- cheaper with 1x generate_series() !
SELECT ts AS ts1, ts + interval '4 hours' AS ts2
FROM generate_series(timestamp '2023-01-01 12:00', timestamp '2023-01-02 16:00', '1 hour') ts
)
SELECT t.ts1, sum(f.count) AS total
FROM times t
JOIN foo f ON f.ts >= t.ts1 AND f.ts < t.ts2 -- ! exclude upper bound
GROUP BY t.ts1;
| QUERY PLAN |
|---|
| HashAggregate (cost=25320.34..25322.34 rows=200 width=16) (actual time=0.119..0.120 rows=0 loops=1) |
| Group Key: ts.ts |
| Batches: 1 Memory Usage: 40kB |
| -> Nested Loop (cost=0.16..22237.00 rows=616667 width=12) (actual time=0.116..0.117 rows=0 loops=1) |
| -> Function Scan on generate_series ts (cost=0.00..10.00 rows=1000 width=8) (actual time=0.020..0.024 rows=29 loops=1) |
| -> Append (cost=0.15..16.05 rows=618 width=12) (actual time=0.003..0.003 rows=0 loops=29) |
| -> Index Scan using foo_p1_pkey on foo_p1 f_1 (cost=0.15..4.32 rows=206 width=12) (actual time=0.002..0.002 rows=0 loops=12) |
| Index Cond: ((ts >= ts.ts) AND (ts < (ts.ts + '04:00:00'::interval))) |
| -> Index Scan using foo_p2_pkey on foo_p2 f_2 (cost=0.15..4.32 rows=206 width=12) (actual time=0.001..0.001 rows=0 loops=20) |
| Index Cond: ((ts >= ts.ts) AND (ts < (ts.ts + '04:00:00'::interval))) |
| -> Index Scan using foo_p3_pkey on foo_p3 f_3 (cost=0.15..4.32 rows=206 width=12) (never executed) |
| Index Cond: ((ts >= ts.ts) AND (ts < (ts.ts + '04:00:00'::interval))) |
| Planning Time: 0.821 ms |
| Execution Time: 0.353 ms |
EXPLAIN