Haas: New Features Coming in PostgreSQL 10

Posted Apr 11, 2017 8:28 UTC (Tue) by ovitters (guest, #27950)
In reply to: Haas: New Features Coming in PostgreSQL 10 by brunowolff
Parent article: Haas: New Features Coming in PostgreSQL 10

Can Postgres automatically figure out the best index type? Having to know the details (drawbacks, benefits) of all the various index types seems a bit too much. Sometimes it is nicer if by default it does the likely right thing, then still allowing you to tweak it later. Ideally any re-tweak should be possible automatically as well.

Haas: New Features Coming in PostgreSQL 10

Posted Apr 11, 2017 12:32 UTC (Tue) by brunowolff (guest, #71160) [Link]

Normally a particular operator class is going to have a default index type you're going to want to use. It looks like the Gist index for inet was known to be poor and someone worked on an SP-Gist index to try to improve the situation. (Which it did for me.) I suspect at this time they will need to keep the Gist index around for backward compatibility for a while, as the default index for inet goes back from before there was a Gist index for it. So there seems to be a precedent for not dropping index support.
This might be more complicated going forward with the improvements in hash indexes also coming in version 10. Previously b-tree indexes were typically faster and provided better crash safeness than hash indexes, so that for operators with b-tree support you would choose b-tree. But it looks like their might be some cases where you'd prefer hash indexes going forward. B-tree will probably still be the safe default, but if you really needed to try to get things faster, you might want to run benchmarks to see if a hash index would be better in a particular case.

Haas: New Features Coming in PostgreSQL 10

Posted Apr 21, 2017 15:21 UTC (Fri) by intgr (subscriber, #39733) [Link]

Short answer: No. Not in the general case because it doesn't know what kinds of queries you intend to run. Different indexes support different operators. Even in the simple case of text and numeric values, you can have hash indexes -- which only support equality (=) queries -- or btree indexes, which support range queries (>, <, <=, etc), ORDER BY and can speed up merge joins.

Then there are BRIN indexes, which trade off faster update speed and smaller disk size for slower lookup times. Similarly GIN and GiST support some overlapping operator classes, but have different performance tradeoffs.

For the 'inet' type as well, if all you want is range queries, then btree is better than SP-GiST. If you need the more complicated operators then both GiST and SP-GiST provide almost the same set of operators. I don't understand them enough to say whether there's a useful tradeoff involved or one is clearly better than the other. If the latter, then keeping the old GiST implementation around sounds like a misfeature.