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

Postgres Open 2019
Sep 11-13, 2019
Orlando, FL, USA
PGConf.EU 2019
Oct 15-18, 2019
Milan, Italy

Past

Postgres London 2019
Jul 2-3, 2019
London, UK
PGCon 2019
May 27-31, 2019
Ottawa, Canada
PGDay.IT 2019
May 17, 2019
Bologna, Italy
PGConf.DE 2019
May 10, 2019
Leipzig, Germany
FOSS North 2019
Apr 8-9, 2019
Gothenburg, Sweden
More past conferences