Postgres hstore: GIN vs GiST index performance

Question

I have to decide whether to use GIN or GiST indexing for an hstore column.

The Postgres docs state:

• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are about ten times slower to update than GiST
• GIN indexes are two-to-three times larger than GiST

The way I interpret it, use GIN if you need to query a lot, use GiST if you need to update a lot.

In this test, all of the three disadvantages of GIN over GiST mentioned above are confirmed. However, other than suggested in the Postgres docs, the advantage of GIN over GiST (faster lookup) is very small. Slide 53 shows that in the test GIN was only 2% to 3% faster as opposed to 200% to 300% suggested in the Postgres docs.

Which source of information is more reliable and why?

Severini

I'd go further and say "Use GiST unless you have essentially static data". Part of the perf difference may be to do with the RAM you've got; benefits of GIN indexes go away if the whole index (bigger than GiST) doesn't fit in RAM. – Psychomancy

Answer 1

The documents state what the situation is "in general".

However, you aren't running PostgreSQL "in general", you are running it on specific hardware with a specific pattern of use.

So - if you care a lot, then you'll want to test it yourself. A GiST index will always require re-checking its condition. However if the queries you run end up doing further checks anyway, a GIN index might not win there. Also there are all the usual issues around cache usage etc.

For my usage, on smaller databases with moderate update rates, I've been happy enough with GiST. I've seen a 50% improvement in speed with GIN (across a whole query), but it's not been worth the slower indexing. If I was building a huge archive server it might be different.

Xiaoximena