I just noticed this old thread.
Thomas Kellerer wrote:
> > 62.1. Introduction
> > ...
> > "A block range is a group of pages that are physically adjacent in the table; for each block range, some summary
infois 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?
>
> That is something I am also curious about. If that was true, it would
> mean that BRIN indexes couldn't be used on tables that are not
> clustered along the index
There's no hard requirement that values must be clustered. If the
values are clustered, that's the best case scenario for BRIN and things
will be very quick. However, clustering a table is a slow operation and
requires locking the table, so I don't recommend that.
But strict correlation isn't really necessary either -- you just need
the values to be grouped together. To illustrate, consider this
simplistic case: table has four pages, all the values in the first page
have col1=999, page 2 has all col1=1, page 3 has all col1=1500, page 4
has col1=-1000. There's little correlation there, but a BRIN index with
pages_per_range=1 can still help a query that looks for col1 > 500
execute optimally.
> it wouldn't make sense to have more than one BRIN index.
Well, you can put all the columns in a single index, and it works just
like if you had one index for each column. However, if you want a BRIN
index that's more detailed for certain columns than others, you can use
different pages_per_range settings on multiple indexes.
--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services