Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

PostgreSQL BRIN index WITH pages_per_range

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
vacuum analyze;
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;
show plan split remove clear
name size pages rows/page pages/`a`
foo 15 MB 1912 135 73
create index foo_brin_2 on foo using brin(a) with (pages_per_range=16);
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
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

Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How to configure Replication Manager (repmgr) ?

What's is the difference between streaming replication Vs hot standby vs warm standby ?

Script to kill ALL IDLE Connection In postgreSQL