Re: BRIN indexes

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Felipe Santos
Sent: Thursday, January 28, 2016 1:17 PM
To: Joshua D. Drake <jd(at)commandprompt(dot)com>
Cc: Melvin Davidson <melvin6925(at)gmail(dot)com>; David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>; pgsql-general(at)postgresql(dot)org; Thomas Kellerer <spam_eater(at)gmx(dot)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"
Link: http://www.postgresql.org/docs/devel/static/brin-intro.html

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