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 FUNCTION
QUERY PLAN |
---|
WindowAgg (cost=0.29..6518.25 rows=100000 width=16) (actual rows=100000 loops=1) |
-> Index Scan using idx_foo on foo (cost=0.29..4768.25 rows=100000 width=12) (actual rows=100000 loops=1) |
Planning time: 0.519 ms |
Execution time: 693.124 ms |
EXPLAIN
QUERY PLAN |
---|
WindowAgg (cost=0.29..10018.25 rows=100000 width=28) (actual rows=100000 loops=1) |
-> Subquery Scan on dt (cost=0.29..7518.25 rows=100000 width=20) (actual rows=100000 loops=1) |
-> WindowAgg (cost=0.29..6518.25 rows=100000 width=20) (actual rows=100000 loops=1) |
-> Index Scan using idx_foo on foo (cost=0.29..4768.25 rows=100000 width=12) (actual rows=100000 loops=1) |
Planning time: 0.096 ms |
Execution time: 158.436 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on bar (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1) |
Planning time: 0.028 ms |
Execution time: 225.195 ms |
EXPLAIN
QUERY PLAN |
---|
WindowAgg (cost=0.29..9518.25 rows=100000 width=16) (actual rows=100000 loops=1) |
-> Subquery Scan on sub (cost=0.29..7518.25 rows=100000 width=16) (actual rows=100000 loops=1) |
-> WindowAgg (cost=0.29..6518.25 rows=100000 width=20) (actual rows=100000 loops=1) |
-> Index Scan using idx_foo on foo (cost=0.29..4768.25 rows=100000 width=12) (actual rows=100000 loops=1) |
Planning time: 0.094 ms |
Execution time: 171.326 ms |
EXPLAIN
CREATE FUNCTION
QUERY PLAN |
---|
Function Scan on f_special_rolling_sum (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1) |
Planning time: 0.027 ms |
Execution time: 170.729 ms |
EXPLAIN
CREATE INDEX
VACUUM
QUERY PLAN |
---|
WindowAgg (cost=0.42..4802.42 rows=100000 width=12) (actual rows=100000 loops=1) |
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1) |
Heap Fetches: 0 |
Planning time: 0.115 ms |
Execution time: 301.848 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on bar (cost=0.25..10.25 rows=1000 width=16) (actual rows=100000 loops=1) |
Planning time: 0.027 ms |
Execution time: 184.935 ms |
EXPLAIN
QUERY PLAN |
---|
Function Scan on f_special_rolling_sum (cost=0.25..10.25 rows=1000 width=12) (actual rows=100000 loops=1) |
Planning time: 0.026 ms |
Execution time: 130.352 ms |
EXPLAIN
QUERY PLAN |
---|
WindowAgg (cost=0.42..8302.42 rows=100000 width=28) (actual rows=100000 loops=1) |
-> Subquery Scan on dt (cost=0.42..5802.42 rows=100000 width=20) (actual rows=100000 loops=1) |
-> WindowAgg (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1) |
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1) |
Heap Fetches: 0 |
Planning time: 0.103 ms |
Execution time: 111.176 ms |
EXPLAIN
QUERY PLAN |
---|
WindowAgg (cost=0.42..7802.42 rows=100000 width=16) (actual rows=100000 loops=1) |
-> Subquery Scan on sub (cost=0.42..5802.42 rows=100000 width=16) (actual rows=100000 loops=1) |
-> WindowAgg (cost=0.42..4802.42 rows=100000 width=20) (actual rows=100000 loops=1) |
-> Index Only Scan using idx_foo_covering on foo (cost=0.42..3052.42 rows=100000 width=12) (actual rows=100000 loops=1) |
Heap Fetches: 0 |
Planning time: 0.101 ms |
Execution time: 104.355 ms |
EXPLAIN