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. 756624 fiddles created (13585 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,100000) b) b order by a; create index foo_btree_covering on foo(a,b); create index foo_btree on foo(a); create index foo_gin on foo using gin(a); create index foo_brin_2 on foo using brin(a) with (pages_per_range=2); create index foo_brin_4 on foo using brin(a) with (pages_per_range=4);
2600000 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" 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
foo 149 MB 19118 135
foo_btree_covering 56 MB 7132 364
foo_btree 56 MB 7132 364
foo_gin 2928 kB 366 7103
foo_brin_2 264 kB 33 78787
foo_brin_4 136 kB 17 152941
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=3120.83..3120.84 rows=1 width=8) (actual time=21.042..21.042 rows=1 loops=1)
-> Index Only Scan using foo_btree_covering on foo (cost=0.43..2869.28 rows=100620 width=4) (actual time=0.035..15.023 rows=100000 loops=1)
Index Cond: (a = 'a'::text)
Heap Fetches: 0
Planning time: 0.122 ms
Execution time: 21.062 ms
 hidden batch(es)


drop index foo_btree_covering;
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=3863.83..3863.84 rows=1 width=8) (actual time=27.379..27.380 rows=1 loops=1)
-> Index Scan using foo_btree on foo (cost=0.43..3612.28 rows=100620 width=4) (actual time=0.037..19.404 rows=100000 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.114 ms
Execution time: 27.403 ms
 hidden batch(es)


drop index foo_btree;
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=21475.10..21475.11 rows=1 width=8) (actual time=24.391..24.391 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=847.80..21223.55 rows=100620 width=4) (actual time=2.519..17.148 rows=100000 loops=1)
Recheck Cond: (a = 'a'::text)
Rows Removed by Index Recheck: 96
Heap Blocks: lossy=736
-> Bitmap Index Scan on foo_brin_4 (cost=0.00..822.65 rows=100620 width=0) (actual time=2.504..2.504 rows=7360 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.157 ms
Execution time: 24.432 ms
 hidden batch(es)


drop index foo_brin_4;
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=21539.10..21539.11 rows=1 width=8) (actual time=25.484..25.485 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=911.80..21287.55 rows=100620 width=4) (actual time=4.978..18.147 rows=100000 loops=1)
Recheck Cond: (a = 'a'::text)
Rows Removed by Index Recheck: 96
Heap Blocks: lossy=736
-> Bitmap Index Scan on foo_brin_2 (cost=0.00..886.65 rows=100620 width=0) (actual time=4.969..4.969 rows=7360 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.102 ms
Execution time: 25.523 ms
 hidden batch(es)


drop index foo_brin_2;
 hidden batch(es)


explain analyze select sum(b) from foo where a='a';
QUERY PLAN
Aggregate (cost=21559.10..21559.11 rows=1 width=8) (actual time=26.421..26.421 rows=1 loops=1)
-> Bitmap Heap Scan on foo (cost=931.80..21307.55 rows=100620 width=4) (actual time=10.143..18.572 rows=100000 loops=1)
Recheck Cond: (a = 'a'::text)
Heap Blocks: exact=736
-> Bitmap Index Scan on foo_gin (cost=0.00..906.65 rows=100620 width=0) (actual time=10.068..10.068 rows=100000 loops=1)
Index Cond: (a = 'a'::text)
Planning time: 0.092 ms
Execution time: 26.449 ms
 hidden batch(es)