4 ms·
It appears the author was only looking at gist indexes (which AFAICT haven't got faster in 9.4), rather than gin indexes (which have). Gin indexes can certainl
by radiowave 12y ago
It appears the author was only looking at gist indexes (which AFAICT haven't got faster in 9.4), rather than gin indexes (which have).
Gin indexes can certainly be used with the trigram extension, in exactly the same way as the gist ones, and you would generally tend to expect gin to be faster for queries, but slower for updates and inserts, compared to gist, though I've never done a serious comparison on trigram data.
- pgaddict 12y agoIn my experience the wisdom "GIN is slower to update than GiST" is mostly obsolete nowadays. At least that's what my benchmark results suggest http://blog.pgaddict.com/posts/performance-since-postgresql-7-4-to-9-4-fulltext http://blog.pgaddict.com/posts/performance-since-postgresql-... It's also a bit shortsighted to look at the index maintenance in isolation - when your workload is 99% reads, and queries with GIN indexes are way faster, it's probably more efficient to use GIN indexes. Moreover, there are often more expensive parts when inserting/updating data (either inside the database, or in the application), so the index maintenance is not really the main problem. I'm sure there are workloads where it's still true, though.
- radiowave 12y agoRight. I've had one case where index rebuild times kind of mattered, and gist was definitely a lot faster at rebuilding, though not enough to make it worth taking the hit on read performance. But this was pre-9.4, so it may be rather different now. The nature of data being indexed is probably the biggest determinant: gin is really happy when you have a small number of distinct values across a large dataset; I guess at the opposite end of the scale gist might come into it's own.