• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Office Space
    • Virtual Space
    • Co-working Space
    • Private Space
    • Meeting Room
    • Locations
    • Add Listing
    • Dashboard
  • Blogs
  • Careers
    • Jobseeker
    • Employer
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8148383856
    info@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Office Space
      • Virtual Space
      • Co-working Space
      • Private Space
      • Meeting Room
      • Locations
      • Add Listing
      • Dashboard
    • Blogs
    • Careers
      • Jobseeker
      • Employer
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • How to Monitor with PostgreSQL Indexes

      How to Monitor with PostgreSQL Indexes

      • Posted by Nijamutheen J
      • Categories Blog
      • Date January 16, 2019
      • Comments 0 comment

      BTree

      My issue here is that the BTree index will be huge since afaict it will store duplicate values (it has too, since it can’t assume the table is physically sorted). If the BTree is huge I end up having to read both the index and the parts of the table that the index points too…
      Not necessarily — Having a btree index that is ‘covering’ will be the fastest read time, and if that is all you want (ie if you can afford the extra storage), then it is your best bet.

      BRIN

      My understanding is that I can have a small index here at the expense of reading useless pages. Using a small pages_per_range means that the index is bigger (which is a problem with BRIN since I need to read the whole index), having a big pages_per_range means that I’ll read a lot of useless pages.
      If you can’t afford the storage overhead of a covering btree index, BRIN is ideal for you, because you have clustering already in place (this is crucial for BRIN to be useful). BRIN indexes are tiny, so all the pages are likely to be in memory if you choose a suitable value of pages_per_range.
      Is there a magic formula to find a good value of pages_per_range that takes into account those trade offs?
      No magic formula, but start with pages_per_range somewhat less than the average size (in pages) occupied by the average a value. You are probably trying to minimize: (number of BRIN pages scanned)+(number of heap pages scanned) for a typical query. Look for Heap Blocks: lossy=n in the execution plan for pages_per_range=1 and compare with other values for pages_per_range — i.e. see how many unnecessary heap blocks are being scanned.

      GIN/GiST

      Not sure those are relevant here since they’re mostly used for full text search, but I also hear that they’re good at dealing with duplicate keys. Would either a GIN/GiST index help here?
      GIN may be worth considering, but probably not GiST — however if the natural clustering really is good, then BRIN will probably be a better bet.
      Here is a sample comparison between the different index types for dummy data a bit like yours:
      table and indexes:
      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);
      vacuum analyze;
      relation sizes:
      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
      
      covering btree:
      explain analyze select sum(b) from foo where a='a';
      | QUERY PLAN                                                                                                                                      |
      | :---------------------------------------------------------------------------------------------------------------------------------------------- |
      | Aggregate  (cost=3282.57..3282.58 rows=1 width=8) (actual time=45.942..45.942 rows=1 loops=1)                                                   |
      |   ->  Index Only Scan using foo_btree_covering on foo  (cost=0.43..3017.80 rows=105907 width=4) (actual time=0.038..27.286 rows=100000 loops=1) |
      |         Index Cond: (a = 'a'::text)                                                                                                             |
      |         Heap Fetches: 0                                                                                                                         |
      | Planning time: 0.099 ms                                                                                                                         |
      | Execution time: 45.968 ms                                                                                                                       |
      
      plain btree:
      drop index foo_btree_covering;
      explain analyze select sum(b) from foo where a='a';
      | QUERY PLAN                                                                                                                        |
      | :-------------------------------------------------------------------------------------------------------------------------------- |
      | Aggregate  (cost=4064.57..4064.58 rows=1 width=8) (actual time=54.242..54.242 rows=1 loops=1)                                     |
      |   ->  Index Scan using foo_btree on foo  (cost=0.43..3799.80 rows=105907 width=4) (actual time=0.037..33.084 rows=100000 loops=1) |
      |         Index Cond: (a = 'a'::text)                                                                                               |
      | Planning time: 0.135 ms                                                                                                           |
      | Execution time: 54.280 ms                                                                                                         |
      
      BRIN pages_per_range=4:
      drop index foo_btree;
      explain analyze select sum(b) from foo where a='a';
      | QUERY PLAN                                                                                                                        |
      | :-------------------------------------------------------------------------------------------------------------------------------- |
      | Aggregate  (cost=21595.38..21595.39 rows=1 width=8) (actual time=52.455..52.455 rows=1 loops=1)                                   |
      |   ->  Bitmap Heap Scan on foo  (cost=888.78..21330.61 rows=105907 width=4) (actual time=2.738..31.967 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..862.30 rows=105907 width=0) (actual time=2.720..2.720 rows=7360 loops=1) |
      |               Index Cond: (a = 'a'::text)                                                                                         |
      | Planning time: 0.101 ms                                                                                                           |
      | Execution time: 52.501 ms                                                                                                         |
      
      BRIN pages_per_range=2:
      drop index foo_brin_4;
      explain analyze select sum(b) from foo where a='a';
      | QUERY PLAN                                                                                                                        |
      | :-------------------------------------------------------------------------------------------------------------------------------- |
      | Aggregate  (cost=21659.38..21659.39 rows=1 width=8) (actual time=53.971..53.971 rows=1 loops=1)                                   |
      |   ->  Bitmap Heap Scan on foo  (cost=952.78..21394.61 rows=105907 width=4) (actual time=5.286..33.492 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..926.30 rows=105907 width=0) (actual time=5.275..5.275 rows=7360 loops=1) |
      |               Index Cond: (a = 'a'::text)                                                                                         |
      | Planning time: 0.095 ms                                                                                                           |
      | Execution time: 54.016 ms                                                                                                         |
      
      GIN:
      drop index foo_brin_2;
      explain analyze select sum(b) from foo where a='a';
      | QUERY PLAN                                                                                                                         |
      | :--------------------------------------------------------------------------------------------------------------------------------- |
      | Aggregate  (cost=21687.38..21687.39 rows=1 width=8) (actual time=55.331..55.331 rows=1 loops=1)                                    |
      |   ->  Bitmap Heap Scan on foo  (cost=980.78..21422.61 rows=105907 width=4) (actual time=12.377..33.956 rows=100000 loops=1)        |
      |         Recheck Cond: (a = 'a'::text)                                                                                              |
      |         Heap Blocks: exact=736                                                                                                     |
      |         ->  Bitmap Index Scan on foo_gin  (cost=0.00..954.30 rows=105907 width=0) (actual time=12.271..12.271 rows=100000 loops=1) |
      |               Index Cond: (a = 'a'::text)                                                                                          |
      | Planning time: 0.118 ms                                                                                                            |
      | Execution time: 55.366 ms

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      PostgreSQL Multile Schema Backup & Restore,Backup Script,Restoring Script,Backup & Restore Prerequest & PostRequest
      January 16, 2019

      Next post

      How to work with Linux top command ?
      January 16, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      HOW VIRTUAL OFFICE IS GOING TO CHANGE YOUR BUSINESS STRATEGIES
      21Jun2022
      Take the best advantage of your Virtual Office
      21Jun2022
      Let’s discuss how corporates can improve their productivity through virtual office spaces?
      21Jun2022
      Virtual Office Space & Address Services
      21Jun2022
      HOW VIRTUAL OFFICES ARE TRANSFORMING BUSINESSES
      21Jun2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google

      Login with your site account

      Connect with



      Lost your password?