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 table foo as
select bar::integer, lpad('',1000,'A') baz
from generate_series(1,1e4) bar
order by bar; -- BRIN friendly clustering
SELECT 10000
-- N.B. "Rows Removed by Index Recheck"
create index foo_brin on foo using brin(bar);
CREATE INDEX
vacuum analyze;
VACUUM
explain analyze select * from foo where bar between 1 and 100;
| QUERY PLAN |
|---|
| Bitmap Heap Scan on foo (cost=13.01..323.85 rows=99 width=1008) (actual time=0.108..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.100..0.100 rows=1280 loops=1) |
| Index Cond: ((bar >= 1) AND (bar <= 100)) |
| Planning time: 0.338 ms |
| Execution time: 0.415 ms |
EXPLAIN
drop table foo;
DROP TABLE
create table foo as
select bar::integer, lpad('',1000,'A') baz
from generate_series(1,1e4) bar
order by random(); -- _not_ BRIN friendly
SELECT 10000
create index foo_brin on foo using brin(bar);
CREATE INDEX
vacuum analyze;
VACUUM
-- N.B. "Rows Removed by Index Recheck"
explain analyze select * from foo where bar between 1 and 100;
| QUERY PLAN |
|---|
| Bitmap Heap Scan on foo (cost=13.01..323.85 rows=99 width=1008) (actual time=0.167..2.761 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.113..0.113 rows=14290 loops=1) |
| Index Cond: ((bar >= 1) AND (bar <= 100)) |
| Planning time: 0.142 ms |
| Execution time: 2.806 ms |
EXPLAIN