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
CREATE INDEX
INSERT 0 1000
CREATE TABLE
CREATE INDEX
INSERT 0 81083
| QUERY PLAN |
|---|
| GroupAggregate (cost=0.57..6392.08 rows=200 width=12) (actual time=0.386..133.881 rows=854 loops=1) |
| Group Key: sales_periods.product_id |
| -> Nested Loop (cost=0.57..5957.53 rows=86511 width=8) (actual time=0.077..97.707 rows=243454 loops=1) |
| -> Index Scan using index_ranges on sales_periods (cost=0.28..93.78 rows=1700 width=12) (actual time=0.009..0.697 rows=1000 loops=1) |
| -> Index Only Scan using days_index on calendar (cost=0.29..2.94 rows=51 width=4) (actual time=0.010..0.072 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: 243454 |
| Planning Time: 0.606 ms |
| Execution Time: 134.368 ms |
EXPLAIN