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.row...@2ndquadrant.com > wrote: > On 29 January 2016 at 06:10, Melvin Davidson <melvin6...@gmail.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.
