Hacker News new | ask | show | jobs
by ankimal 3818 days ago
http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN index is all about.
1 comments

Out of curiosity, has anyone managed to find something more detailed about the guts?

It sounds like it's basically a BTree that stops branching at a certain threshold, but I'm almost certainly wrong.

No, that's not really it, although you could see it as a very degenerate form of a btree. Basically it's using clustering inherent to the data - say a mostly increasing timestamp, autoincrement id, model number ... - to build a coarse map of the contents. E.g. saying "pages from 0 to 16 have the date range 2011-11 to 2011-12" and "pages from 16 to 48 have the date range 2012-01-01 to 2012-01-13". With such range maps (where obviously several overlapping ranges can exist) you can build a small index over large amounts of data.

Obviously single row accesses in a fully cached workload are going to be faster if done via a btree rather than such range maps, even if there's perfect clustering. But the price for having such an index is much lower, allowing you to have many more indexes. Additionally it can even be more efficient to access via BRIN if you access more than one row, due to fewer pages needing to be touched.

There was a talk about index internals at pgconf.eu that also covered the new BRIN indexes.

Slides are here: http://hlinnaka.iki.fi/presentations/Index-internals-Vienna2...