3 ms·
B-tree for most ‘normal’ data. You mostly only need the other indexes for cases where your data isn’t just simple values, like if your column contains stemmed s
by Alex3917 9y ago
B-tree for most ‘normal’ data. You mostly only need the other indexes for cases where your data isn’t just simple values, like if your column contains stemmed search terms or arrays or JSON.
If you’re using ORM, then your ORM documentation should be telling you which index is the most appropriate if it isn’t a b-tree.
There are also some corner cases where you can see a speed up if you have tons of data that’s being written in an append-only manner, but for the most part b-trees are the way to go for chars, ints, bools, etc.
- rjeli 9y agoI haven't found an explanation of when to use BRIN indexes that works with my brain. anyone have a good link?
- Alex3917 9y agoFrom Wikipedia, it sounds like the time to use it is when: - You have enough a huge amount of data (hundreds of millions or more rows) - You want to index a column whose values are never changed, and are monotonically increasing (e.g. a created_at datetime field).
- _wmd 9y agoI wrote http://pythonsweetness.tumblr.com/post/119568339102/block-range-brin-indexes-in-postgresql-95 http://pythonsweetness.tumblr.com/post/119568339102/block-ra... because I had the same problem, maybe see if it works for you
- rjeli 9y agoAwesome, thank you