clear markdown compare help donate comments/suggestions/bugs a leap of faith? diddy dollings
clear markdown donate comments/suggestions/bugs a leap of faith?
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 432219 distinct fiddles created so far.

create table foo as select bar::integer, lpad('',1000,'A') baz from generate_series(1,1e4) bar order by bar; -- BRIN friendly clustering
10000 rows affected
 hidden batch(es)


-- N.B. "Rows Removed by Index Recheck" create index foo_brin on foo using brin(bar);
 hidden batch(es)


vacuum analyze;
 hidden batch(es)


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.078..1.042 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.058..0.058 rows=1280 loops=1)
Index Cond: ((bar >= 1) AND (bar <= 100))
Planning time: 0.211 ms
Execution time: 1.090 ms
 hidden batch(es)


drop table foo;
 hidden batch(es)


create table foo as select bar::integer, lpad('',1000,'A') baz from generate_series(1,1e4) bar order by random(); -- _not_ BRIN friendly
10000 rows affected
 hidden batch(es)


create index foo_brin on foo using brin(bar);
 hidden batch(es)


vacuum analyze;
 hidden batch(es)


-- 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.293..8.580 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.167..0.167 rows=15360 loops=1)
Index Cond: ((bar >= 1) AND (bar <= 100))
Planning time: 0.158 ms
Execution time: 8.643 ms
 hidden batch(es)