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. 2043036 fiddles created (16636 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=2152.12..2152.13 rows=1 width=8) (actual time=5.010..5.010 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=89.71..2127.05 rows=10027 width=4) (actual time=0.156..3.296 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.21 rows=10027 width=0) (actual time=0.125..0.126 rows=800 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 2.684 ms
Execution time: 5.056 ms
 hidden batch(es)