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?.
version |
---|
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit |
1 rows affected
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350051.28 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.079 ms |
Execution Time: 948.344 ms |
QUERY PLAN |
---|
ProjectSet (cost=128.56..5128.59 rows=1000000 width=36) (actual rows=1000000 loops=1) |
InitPlan 1 (returns $0) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
InitPlan 2 (returns $1) |
-> Seq Scan on test test_1 (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
-> Aggregate (cost=83.16..83.17 rows=1 width=32) (actual rows=1 loops=1) |
-> Nested Loop (cost=0.01..73.63 rows=3810 width=40) (actual rows=4 loops=1) |
-> Function Scan on unnest x (cost=0.01..0.13 rows=3 width=40) (actual rows=4 loops=1) |
Filter: (num > 1) |
Rows Removed by Filter: 1 |
-> Materialize (cost=0.00..29.05 rows=1270 width=0) (actual rows=1 loops=4) |
-> Seq Scan on test t (cost=0.00..22.70 rows=1270 width=0) (actual rows=1 loops=1) |
Planning Time: 0.604 ms |
Execution Time: 1302.000 ms |
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350041.75 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.078 ms |
Execution Time: 1434.311 ms |
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350079.85 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.093 ms |
Execution Time: 1999.928 ms |
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350070.33 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.086 ms |
Execution Time: 2255.108 ms |
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350070.33 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.086 ms |
Execution Time: 2281.977 ms |
QUERY PLAN |
---|
ProjectSet (cost=0.00..6350035.40 rows=1270000000 width=36) (actual rows=1000000 loops=1) |
-> Seq Scan on test (cost=0.00..22.70 rows=1270 width=32) (actual rows=1 loops=1) |
Planning Time: 0.073 ms |
Execution Time: 3400.580 ms |