Re: BRIN indexes

2016-01-28 16:03 GMT-02:00 Joshua D. Drake <jd(at)commandprompt(dot)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(at)postgresql(dot)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.