Обсуждение: BRIN indexes
Reposting because I received no reply from a previous inquiry as "9.5 new features".
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?
--
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?
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
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.On Thu, Jan 28, 2016 at 12:31 PM, David Rowley <david.rowley@2ndquadrant.com> wrote:
On 29 January 2016 at 06:10, Melvin Davidson <melvin6925@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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.