Re: BRIN indexes

Melvin Davidson wrote:

> With regard to BRIN indexes:
>
> http://www.postgresql.org/docs/9.5/interactive/brin-intro.html
>
> 62.1. Introduction
> ....
> "A block range is a group of pages that are physically adjacent in the
> table; for each block range, some summary info is stored by the index."
>
> From the above, may I presume that it is best to cluster (or sort), the
> table based on the intended
> BRIN column(s) before actually creating the index to insure the pages are
> adjacent? If so, should
> that not be included in the documentation, instead of implied?

The issue is that you cannot normally afford to cluster a table every
once in a while; if the natural order in which data is loaded isn't good
for BRIN, then perhaps you shouldn't consider BRIN at all. If you're
bulk-loading and then create a BRIN index, then it's better to load the
data in order of the columns. But perhaps you have reasons to have the
table sorted in some other order, in which case trying to satisfy BRIN
would be worse.

All in all, I think there are enough caveats about this that I'm not
sure about putting it up in the doc.

I don't have faith in CLUSTER anyway. Taking exclusive locks and all.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services