2016-01-28 16:03 GMT-02:00 Joshua D. Drake <j...@commandprompt.com>: > On 01/28/2016 09:41 AM, Melvin Davidson wrote: > >> 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. >> > > BRIN indexes are best used on INSERT only tables with a sequence of > numbers as a PK or indexed column that will be queried against. At least as > I understand it. > > JD > > -- > Command Prompt, Inc. http://the.postgres.company/ > +1-503-667-4564 > PostgreSQL Centered full stack support, consulting and development. > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
"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." Not really, if both columns are ordered, BRIN will work "Therefore, it actually would be good to state that in the documentation, even it were just a comment." It is = "BRIN is designed for handling very large tables in which certain columns have some natural correlation with their physical location within the table" Link: http://www.postgresql.org/docs/devel/static/brin-intro.html Also, I did some tests and here are the results I got: Query with no index = completion time 43s Same Query with BRIN = completion time 14s / index size 0,5 MB Same Query without BRIN and with BTREE = completion time 10s / index size 5.000,00 MB As you can see, BRIN can save 99% of disk space for just a slightly worse performance. It seems like a huge improvement, given that your data fits BRIN's use case.
