clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
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. 756645 fiddles created (13606 in the last week).

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=2154.09..2154.10 rows=1 width=8) (actual time=4.519..4.519 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=90.38..2128.81 rows=10114 width=4) (actual time=0.172..3.269 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..87.85 rows=10114 width=0) (actual time=0.147..0.147 rows=800 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.201 ms
Execution time: 4.560 ms
 hidden batch(es)