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?.
1000 rows affected
QUERY PLAN |
---|
GroupAggregate (cost=19866.44..19879.74 rows=200 width=12) (actual time=558.215..858.166 rows=854 loops=1) |
Group Key: sales_periods.product_id |
-> Sort (cost=19866.44..19869.26 rows=1130 width=12) (actual time=557.848..632.362 rows=243016 loops=1) |
Sort Key: sales_periods.product_id |
Sort Method: external merge Disk: 6200kB |
-> Nested Loop (cost=0.01..19809.13 rows=1130 width=12) (actual time=0.179..313.642 rows=243016 loops=1) |
Join Filter: ((day.day)::date <@ sales_periods.range) |
Rows Removed by Join Filter: 121984 |
-> Function Scan on generate_series day (cost=0.01..10.01 rows=1000 width=8) (actual time=0.149..0.274 rows=365 loops=1) |
-> Materialize (cost=0.00..26.95 rows=1130 width=36) (actual time=0.000..0.133 rows=1000 loops=365) |
-> Seq Scan on sales_periods (cost=0.00..21.30 rows=1130 width=36) (actual time=0.013..0.215 rows=1000 loops=1) |
Planning Time: 0.433 ms |
Execution Time: 870.924 ms |