Postgres Pro
Downloads
Home   >   mailing lists

Re: BRIN indexes - Mailing list pgsql-general

From Igor Neyman
Subject Re: BRIN indexes
Date
Msg-id A76B25F2823E954C9E45E32FA49D70ECCD5E244F@mail.corp.perceptron.com
Whole thread Raw
In response to Re: BRIN indexes  (Felipe Santos <felipepts@gmail.com>)
List pgsql-general
Tree view

 

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Felipe Santos
Sent: Thursday, January 28, 2016 1:17 PM
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Melvin Davidson <melvin6925@gmail.com>; David Rowley <david.rowley@2ndquadrant.com>; pgsql-general@postgresql.org; Thomas Kellerer <spam_eater@gmx.net>
Subject: Re: [GENERAL] BRIN indexes

 

"Further to the point, it is self defeating to have more than one BRIN index on the table if the columns involved would have mutually  non-adjacent pages."

 

   Not really, if both columns are ordered, BRIN will work

 

"Therefore, it actually would be good to state that in the documentation, even it were just a comment."

 

   It is = "BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table"

 

 

Also, I did some tests and here are the results I got:

 

Query with no index = completion time 43s

Same Query with BRIN = completion time 14s / index size 0,5 MB

Same Query without BRIN and with BTREE = completion time 10s / index size 5.000,00 MB

 

As you can see, BRIN can save 99% of disk space for just a slightly worse performance.

 

It seems like a huge improvement, given that your data fits BRIN's use case.

 

Felipe,

 

What kind of queries you used in your test?

Where they based on clustering columns?

 

Regards

Igor Neyman

pgsql-general by date:

Previous
From: Augori
Date:
Subject: Re: Transaction check error installing PostGIS
Next
From: Craig Ringer
Date:
Subject: Re: BDR replication