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. 432217 distinct fiddles created so far.

create table foo(a,b,c) as select *, lpad('',20) from (select chr(g) a from generate_series(97,122) g) a cross join (select generate_series(1,10000) b) b order by a;
260000 rows affected
 hidden batch(es)


vacuum analyze;
 hidden batch(es)


select relname "name", pg_size_pretty(siz) "size", siz/8192 pages, (select count(*) from foo)*8192/siz "rows/page", siz/8192/26 "pages/`a`" from( select relname, pg_relation_size(C.oid) siz from pg_class c join pg_namespace n on n.oid = c.relnamespace where nspname = current_schema ) z;
name size pages rows/page pages/`a`
foo 15 MB 1912 135 73
 hidden batch(es)


create index foo_brin_2 on foo using brin(a) with (pages_per_range=16);
 hidden batch(es)


select relname "name", pg_size_pretty(siz) "size", siz/8192 pages from( select relname, pg_relation_size(C.oid) siz from pg_class c join pg_namespace n on n.oid = c.relnamespace where nspname = current_schema ) z;
name size pages
foo 15 MB 1912
foo_brin_2 24 kB 3
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=2149.36..2149.37 rows=1 width=8) (actual time=4.798..4.798 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=88.77..2124.60 rows=9906 width=4) (actual time=0.107..2.984 rows=10000 loops=1)
Recheck Cond: (a = 'a'::text)
Rows Removed by Index Recheck: 880
Heap Blocks: lossy=80
-> Bitmap Index Scan on foo_brin_2 (cost=0.00..86.30 rows=9906 width=0) (actual time=0.089..0.089 rows=800 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.082 ms
Execution time: 4.821 ms
 hidden batch(es)