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
INSERT 0 1000
ALTER TABLE
CREATE TABLE
INSERT 0 961
ALTER TABLE
CREATE TABLE
INSERT 0 17897
ALTER TABLE
VACUUM
VACUUM
VACUUM
| QUERY PLAN |
|---|
| GroupAggregate (cost=4542.85..4856.63 rows=961 width=12) (actual time=515.018..627.165 rows=854 loops=1) |
| Group Key: sales_periods.product_id |
| -> Sort (cost=4542.85..4644.24 rows=40556 width=8) (actual time=514.856..550.795 rows=243454 loops=1) |
| Sort Key: sales_periods.product_id |
| Sort Method: external merge Disk: 4320kB |
| -> Nested Loop (cost=0.30..1438.77 rows=40556 width=8) (actual time=0.098..362.506 rows=243454 loops=1) |
| -> Seq Scan on sales_periods (cost=0.00..17.00 rows=1000 width=12) (actual time=0.010..0.477 rows=1000 loops=1) |
| -> Memoize (cost=0.30..1.53 rows=41 width=4) (actual time=0.019..0.277 rows=243 loops=1000) |
| Cache Key: sales_periods.since, sales_periods.till |
| Cache Mode: binary |
| Hits: 0 Misses: 1000 Evictions: 521 Overflows: 0 Memory Usage: 4097kB |
| -> Index Only Scan using calendar_pkey on calendar (cost=0.29..1.52 rows=41 width=4) (actual time=0.017..0.098 rows=243 loops=1000) |
| Index Cond: ((day >= sales_periods.since) AND (day <= sales_periods.till) AND (day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date)) |
| Heap Fetches: 0 |
| Planning Time: 0.463 ms |
| Execution Time: 629.069 ms |
EXPLAIN
| QUERY PLAN |
|---|
| GroupAggregate (cost=9112.35..9414.27 rows=961 width=12) (actual time=267.787..358.412 rows=854 loops=1) |
| Group Key: products.product_id |
| -> Sort (cost=9112.35..9209.79 rows=38974 width=8) (actual time=267.653..301.449 rows=236802 loops=1) |
| Sort Key: products.product_id |
| Sort Method: external merge Disk: 4200kB |
| -> Hash Semi Join (cost=29.79..6140.54 rows=38974 width=8) (actual time=0.452..212.840 rows=236802 loops=1) |
| Hash Cond: (products.product_id = sales_periods.product_id) |
| Join Filter: ((calendar.day >= sales_periods.since) AND (calendar.day <= sales_periods.till)) |
| Rows Removed by Join Filter: 118063 |
| -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.041..91.361 rows=350765 loops=1) |
| -> Seq Scan on products (cost=0.00..14.61 rows=961 width=4) (actual time=0.013..0.616 rows=961 loops=1) |
| -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.030 rows=365 loops=961) |
| -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.022..0.072 rows=365 loops=1) |
| Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date)) |
| Heap Fetches: 0 |
| -> Hash (cost=17.00..17.00 rows=1000 width=12) (actual time=0.390..0.391 rows=1000 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 51kB |
| -> Seq Scan on sales_periods (cost=0.00..17.00 rows=1000 width=12) (actual time=0.009..0.231 rows=1000 loops=1) |
| Planning Time: 0.526 ms |
| Execution Time: 359.456 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashAggregate (cost=6335.41..6345.02 rows=961 width=12) (actual time=257.951..258.284 rows=854 loops=1) |
| Group Key: p.product_id |
| Batches: 1 Memory Usage: 193kB |
| -> Hash Semi Join (cost=29.79..6140.54 rows=38974 width=4) (actual time=0.530..205.334 rows=236802 loops=1) |
| Hash Cond: (p.product_id = s.product_id) |
| Join Filter: ((calendar.day >= s.since) AND (calendar.day <= s.till)) |
| Rows Removed by Join Filter: 118063 |
| -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.041..89.445 rows=350765 loops=1) |
| -> Seq Scan on products p (cost=0.00..14.61 rows=961 width=4) (actual time=0.013..0.496 rows=961 loops=1) |
| -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.031 rows=365 loops=961) |
| -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.022..0.074 rows=365 loops=1) |
| Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date)) |
| Heap Fetches: 0 |
| -> Hash (cost=17.00..17.00 rows=1000 width=12) (actual time=0.482..0.483 rows=1000 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 51kB |
| -> Seq Scan on sales_periods s (cost=0.00..17.00 rows=1000 width=12) (actual time=0.008..0.157 rows=1000 loops=1) |
| Planning Time: 0.422 ms |
| Execution Time: 258.377 ms |
EXPLAIN
| QUERY PLAN |
|---|
| HashAggregate (cost=6267.39..6277.00 rows=961 width=12) (actual time=249.521..249.679 rows=854 loops=1) |
| Group Key: p.product_id |
| Batches: 1 Memory Usage: 193kB |
| -> Hash Semi Join (cost=33.41..6086.92 rows=36094 width=4) (actual time=0.607..197.129 rows=236802 loops=1) |
| Hash Cond: (p.product_id = sales_periods.product_id) |
| Join Filter: ((calendar.day >= sales_periods.since) AND (calendar.day <= sales_periods.till)) |
| Rows Removed by Join Filter: 77913 |
| -> Nested Loop (cost=0.29..4415.67 rows=350765 width=8) (actual time=0.042..89.467 rows=350765 loops=1) |
| -> Seq Scan on products p (cost=0.00..14.61 rows=961 width=4) (actual time=0.013..0.701 rows=961 loops=1) |
| -> Materialize (cost=0.29..17.41 rows=365 width=4) (actual time=0.000..0.030 rows=365 loops=961) |
| -> Index Only Scan using calendar_pkey on calendar (cost=0.29..15.59 rows=365 width=4) (actual time=0.022..0.071 rows=365 loops=1) |
| Index Cond: ((day >= '2021-04-14'::date) AND (day <= '2022-04-13'::date)) |
| Heap Fetches: 0 |
| -> Hash (cost=22.00..22.00 rows=890 width=12) (actual time=0.559..0.559 rows=888 loops=1) |
| Buckets: 1024 Batches: 1 Memory Usage: 47kB |
| -> Seq Scan on sales_periods (cost=0.00..22.00 rows=890 width=12) (actual time=0.009..0.278 rows=888 loops=1) |
| Filter: ((since <= '2022-04-13'::date) AND (till >= '2021-04-14'::date)) |
| Rows Removed by Filter: 112 |
| Planning Time: 0.347 ms |
| Execution Time: 249.810 ms |
EXPLAIN
CREATE FUNCTION
COMMENT
| QUERY PLAN |
|---|
| Subquery Scan on sub (cost=65.60..322.96 rows=864 width=8) (actual time=0.575..3.926 rows=854 loops=1) |
| -> GroupAggregate (cost=65.60..89.68 rows=864 width=44) (actual time=0.572..3.153 rows=854 loops=1) |
| Group Key: sales_periods.product_id |
| -> Sort (cost=65.60..67.82 rows=890 width=12) (actual time=0.541..0.627 rows=888 loops=1) |
| Sort Key: sales_periods.product_id |
| Sort Method: quicksort Memory: 66kB |
| -> Seq Scan on sales_periods (cost=0.00..22.00 rows=890 width=12) (actual time=0.013..0.341 rows=888 loops=1) |
| Filter: ((since <= '2022-04-13'::date) AND (till >= '2021-04-14'::date)) |
| Rows Removed by Filter: 112 |
| Planning Time: 0.354 ms |
| Execution Time: 4.010 ms |
EXPLAIN