Re: BRIN indexes

So, IOW, and the answer to my question is yes, it should be insured that
all pages involved are physically adjacent (by design or by pre-sort)
before creating a BRIN on them.
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.
Therefore, it actually would be good to state that in the documentation,
even it were just a comment.

On Thu, Jan 28, 2016 at 12:31 PM, David Rowley <david(dot)rowley(at)2ndquadrant(dot)com
> wrote:

> On 29 January 2016 at 06:10, Melvin Davidson <melvin6925(at)gmail(dot)com> 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?
>
> I personally think the second sentence of the link to the
> documentation covers this quite well. Namely "BRIN is designed for
> handling very large tables in which certain columns have some natural
> correlation with their physical location within the table."
>
> Examples of this might be something like an "orders" table, where you
> have an orderdate column, probably you'll insert into this table as
> orders are received, so quite possibly the table will be naturally
> ordered in ascending orderdate order. Although UPDATEs might create
> new tuples in some free space elsewhere in the relation, but it's not
> hard to imagine other cases where there's no updates and "natural
> correlation" is persisted.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.