Re: BRIN indexes

Поиск
Список
Период
Сортировка
От Felipe Santos
Тема Re: BRIN indexes
Дата
Msg-id CAPYcRiWWhj4dwiZJ8r+5ZSrJVUUn3ZRn9Jkb4dEHQ3Ww_Qk=Yg@mail.gmail.com
обсуждение исходный текст
Ответ на Re: BRIN indexes  ("Joshua D. Drake" <jd@commandprompt.com>)
Ответы Re: BRIN indexes  (Igor Neyman <ineyman@perceptron.com>)
Список pgsql-general


2016-01-28 16:03 GMT-02:00 Joshua D. Drake <jd@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"


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.

В списке pgsql-general по дате отправления:

Предыдущее
От: "Joshua D. Drake"
Дата:
Сообщение: Re: BRIN indexes
Следующее
От: Kevin Waterson
Дата:
Сообщение: Booking Dates and times