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?.
16 rows affected
128000 rows affected
QUERY PLAN |
---|
Sort (cost=26898.01..26930.01 rows=12802 width=20) (actual time=511.933..526.859 rows=64008 loops=1) |
Sort Key: (min(sub2.date)) |
Sort Method: external merge Disk: 2632kB |
-> GroupAggregate (cost=24232.42..26024.65 rows=12802 width=20) (actual time=374.881..463.911 rows=64008 loops=1) |
-> Sort (cost=24232.42..24552.46 rows=128016 width=20) (actual time=374.870..415.777 rows=128016 loops=1) |
Sort Key: sub2.id_type, sub2.grp |
Sort Method: external merge Disk: 4224kB |
-> Subquery Scan sub2 (cost=0.00..10744.34 rows=128016 width=20) (actual time=0.041..230.758 rows=128016 loops=1) |
-> WindowAgg (cost=0.00..9464.18 rows=128016 width=13) (actual time=0.040..192.611 rows=128016 loops=1) |
-> WindowAgg (cost=0.00..6263.78 rows=128016 width=12) (actual time=0.033..111.294 rows=128016 loops=1) |
-> Index Scan using tmp_date_key on tmp (cost=0.00..4023.50 rows=128016 width=12) (actual time=0.022..38.134 rows=128016 loops=1) |
Total runtime: 538.023 ms |
QUERY PLAN |
---|
Sort (cost=39308.50..39340.51 rows=12802 width=20) (actual time=603.614..618.263 rows=64008 loops=1) |
Sort Key: (min(tmp.date)) |
Sort Method: external merge Disk: 2632kB |
-> GroupAggregate (cost=36642.91..38435.14 rows=12802 width=20) (actual time=486.928..559.273 rows=64008 loops=1) |
-> Sort (cost=36642.91..36962.95 rows=128016 width=20) (actual time=486.918..511.419 rows=128016 loops=1) |
Sort Key: tmp.id_type, (((row_number() OVER (?)) - row_number() OVER (?))) |
Sort Method: external sort Disk: 4256kB |
-> WindowAgg (cost=18994.32..21874.68 rows=128016 width=12) (actual time=231.210..344.083 rows=128016 loops=1) |
-> Sort (cost=18994.32..19314.36 rows=128016 width=12) (actual time=231.184..275.473 rows=128016 loops=1) |
Sort Key: tmp.id_type, tmp.date |
Sort Method: external merge Disk: 4224kB |
-> WindowAgg (cost=0.00..5943.74 rows=128016 width=12) (actual time=0.012..94.416 rows=128016 loops=1) |
-> Index Scan using tmp_date_key on tmp (cost=0.00..4023.50 rows=128016 width=12) (actual time=0.009..35.779 rows=128016 loops=1) |
Total runtime: 629.593 ms |
QUERY PLAN |
---|
Function Scan on f_tmp_groups (cost=0.00..260.00 rows=1000 width=20) (actual time=171.841..183.274 rows=64008 loops=1) |
Total runtime: 191.398 ms |