3 ms·

http://www.postgresql.org/docs/9.5/static/brin-intro.html http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN in

by ankimal 11y ago

http://www.postgresql.org/docs/9.5/static/brin-intro.html http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN index is all about.

avita1 11y ago
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.
- anarazel 11y ago
  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.
- jakobegger 11y ago
  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-Vienna2015.pdf http://hlinnaka.iki.fi/presentations/Index-internals-Vienna2...
avita1 11y ago
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.

3 ms·

http://www.postgresql.org/docs/9.5/static/brin-intro.html http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN in

by ankimal 11y ago

http://www.postgresql.org/docs/9.5/static/brin-intro.html http://www.postgresql.org/docs/9.5/static/brin-intro.html If like me you were looking for what BRIN index is all about.

avita1 11y ago
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.
- anarazel 11y ago
  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.
- jakobegger 11y ago
  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-Vienna2015.pdf http://hlinnaka.iki.fi/presentations/Index-internals-Vienna2...
avita1 11y ago
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.