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 Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL