Hacker Newsnew | past | comments | ask | show | jobs | submitlogin

And indexes are faster in 9.4 .

See also the smlar extension (by one of the text search authors). Let's you do TFIDF

I'd kill for BM25 and Solr style filters though...



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.


In 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-...

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.


Right. 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.


> I'd kill for BM25 and Solr style filters though...

In the meantime, I can recommend Sphinx Search: http://en.wikipedia.org/wiki/Sphinx_(search_engine)

(I am not related to it, just a happy user)


Indeed, <10 millisecond search results on 14TB is amazing (absolutely wrecked what we managed to get out of any other indexer - order of magnitude faster). Honestly, Sphinx is a little ugly to setup -- but it is an absolutely amazing piece of technology.

We are currently in the process of shifting everything to RT indexes and have been absolutely delighted with the new beta features.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: