clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2042896 fiddles created (16641 in the last week).

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.089..28.690 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.053..0.053 rows=1280 loops=1)
Index Cond: ((bar >= 1) AND (bar <= 100))
Planning time: 0.537 ms
Execution time: 29.862 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.168..100.656 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.122..0.123 rows=15360 loops=1)
Index Cond: ((bar >= 1) AND (bar <= 100))
Planning time: 0.118 ms
Execution time: 100.712 ms
 hidden batch(es)