19

I have a PostgreSQL 9.1 database that contains 10M+ rows and some text fields that need similarity and %word%-like searches, so I decided to use trigram indexes.

Initially, I started to use GIN indexes, but now I am wondering whether I should rather use GIST.

The theory says:

  • GIN index lookups are about three times faster than GiST
  • GIN indexes take about three times longer to build than GiST
  • GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled (see Section 58.4.1 for details)
  • GIN indexes are two-to-three times larger than GiST indexes

I have tried to create both GIN and GIST indexes and found the following in practice (on my dataset):

  • For LIKE '%word%' queries, GIN is about the same speed (or even 5-20% slower) as GIST for the first time (when the index for the trigrams in the query are not cached yet).
  • For LIKE '%word%' queries, GIN is about 5 times faster than GIST if the trigrams in the query were searched recently. GIST has always the same speed, regardless of cachedness.
  • For % 'word' (similarity) queries, GIN is about 5-8 times slower than GIST, depending on the cachedness of the index.
  • GIN is about 10% smaller than GIST. However, it seems to grow faster if there are UPDATE statements to the table. Unless, of course, I VACUUM FULL often enough.

So I see some discrepancies between theory and practice:

  1. speed: GIST is often faster, except GIN is totally in memory (Can you ensure that in advance?), and even then only for LIKE '%word%' queries.
  2. size: GIN is actually smaller. Is this typical for trigram indexes or is my case an outlier?
  3. May the behaviour be significantly different in 9.4?
  4. A last question: is it wise to use GIN if there are about 10-50k UPDATE statements and 5-10k INSERT statements daily, and weekly VACUUMis probably reasonable (not much usage on the weekend), or will it really hurt UPDATE and INSERT performance?
2
  • 1
    GIN indexes have gotten a lot of improvements in recent versions. What version of PostgreSQL are you using? Commented Aug 25, 2015 at 14:22
  • 1
    9.1 currently, i will add that to the question
    – P.Péter
    Commented Aug 25, 2015 at 14:48

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.