GIN performance (postgresql.org websearch part 2)

As I finished loading the approximately 550,000 emails in our archives into my UTF8 encoded database again (bad encoding earlier), I decided to run a quick benchmark of the different ways of doing fulltext search on it with tsearch2, partially because Devrim requested it. I ran the same query a couple of times to bring things into the cache, and then saw very stable performance on all the queries. The query I ran was:

explain analyze select count(*) from messages where fti @@ plainto_tsquery('magnus hagander');

Which should be simple and self-explaining. I got the following search times:

  • tsearch2 with no index: ~6000 milliseconds (6 seconds)
  • tsearch2 with GiST index: ~250ms (0.25 seconds)
  • tsearch2 with GIN index: ~6ms (very fast)

This shows that tsearch2 with GIN is a lot faster than the GiST version we had in 8.1. Updates are more expensive, but when your dataset has a lot more read than write, then GIN will definitly be a big win.


Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

PGDay.UK
Jul 4, 2017
London, UK
PGDay.RU
Jul 5-7, 2017
St Petersburg, Russia
PGConf.EU 2017
Oct 24-27, 2017
Warsaw, Poland

Past

PGCon 2017
May 23-26, 2017
Ottawa, Canada
FOSS-North
Apr 26, 2017
Gothenburg, Sweden
pgDay.paris 2017
Mar 23, 2017
Paris, France
Nordic PGDay 2017
Mar 21, 2017
Stockholm, Sweden
Confoo Montreal 2017
Mar 8-10, 2017
Montreal, Canada
More past conferences