3 ms·
BRIN (Block Range) Indices look really interesting. Instead of storing the whole B-Tree (and spending time updating it) just store summary of ranges (pages).
by rdtsc 11y ago
BRIN (Block Range) Indices look really interesting.
Instead of storing the whole B-Tree (and spending time updating it) just store summary of ranges (pages).
This for example, would be great for a time series database that is write heavy but not read as often.
I found these benchmarks here explaining the differences:
http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-range-indexes/ http://www.depesz.com/2014/11/22/waiting-for-9-5-brin-block-...
---
Creating 650MB tables:
* btree: 626.859 ms.
* brin: 208.754 ms
(3x speedup)
Updating 30% of values:
* btree: 8398.461 ms.
* brin: 1398.711 ms.
(4x speedup)
Extra bonus:
* size of btree index: 28MB
* size of brin: 64kb
Search (for a range):
$ select count(*) from table where id between 600000::int8 and 650000::int8;
* btree between: 9.574 ms
* brin between: 21.090 ms
---
- saosebastiao 11y agoYep, this is a big deal, for me at least. It basically makes it almost cost-free to add indexes to tables that are write heavy. I've always felt like Bitmap Indexes were a killer feature, and never understood why they weren't used more in databases. If you have a low cardinality column (anything suitable for an enum), your indexes become incredibly fast and cheap.
- pjungwir 11y agoThere was an effort to build bitmap indexes for Postgres a few years ago. There are details in the mailing list archives. It looks like it was almost completed! It's high on my list of things to tackle if I ever get time to start contributing, but maybe someone else will get to it first.
- facetube 11y agoIs that different than the bitmap index access method, which IIRC landed in 8.1 or so, and is used to combine results of multiple index scans?
- pjungwir 11y agoYes, totally different thing. :-) A great book on query plan stuff is here: http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Smith/dp/184951030X http://www.amazon.com/PostgreSQL-High-Performance-Gregory-Sm...