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?.
SELECT 10000
CREATE INDEX
VACUUM
QUERY PLAN |
---|
Bitmap Heap Scan on foo (cost=13.01..323.85 rows=99 width=1008) (actual time=0.109..0.353 rows=100 loops=1) |
Recheck Cond: ((bar >= 1) AND (bar <= 100)) |
Rows Removed by Index Recheck: 796 |
Heap Blocks: lossy=128 |
-> Bitmap Index Scan on foo_brin (cost=0.00..12.99 rows=99 width=0) (actual time=0.101..0.102 rows=1280 loops=1) |
Index Cond: ((bar >= 1) AND (bar <= 100)) |
Planning time: 0.361 ms |
Execution time: 0.415 ms |
EXPLAIN
DROP TABLE
SELECT 10000
CREATE INDEX
VACUUM
QUERY PLAN |
---|
Bitmap Heap Scan on foo (cost=13.01..323.85 rows=99 width=1008) (actual time=0.178..2.803 rows=100 loops=1) |
Recheck Cond: ((bar >= 1) AND (bar <= 100)) |
Rows Removed by Index Recheck: 9900 |
Heap Blocks: lossy=1429 |
-> Bitmap Index Scan on foo_brin (cost=0.00..12.99 rows=99 width=0) (actual time=0.114..0.114 rows=14290 loops=1) |
Index Cond: ((bar >= 1) AND (bar <= 100)) |
Planning time: 0.146 ms |
Execution time: 2.853 ms |
EXPLAIN