Difference between GiST and GIN indexes

Question

I am implementing a table that has a column with a data type of tsvector. What index would be better to use?

In looking through the PostgreSQL documentation here, I seem to get that:

GiST is faster to update and build the index and less accurate than GIN.
GIN is slower to update and build the index, but it is more accurate.

OK, so why would anybody want a GiST indexed field over GIN? If GiST, could give you the wrong results? There must be some advantage (outside performance) on this.

In layman's terms, when would I want to use GIN vs. GiST?

Always provide your version of Postgres. GIN has received major improvements in Postgres 9.4 — Erwin Brandstetter
– Erwin Brandstetter, Commented Mar 10, 2015 at 23:46

Peter Mortensen · Accepted Answer · 2025-07-26 12:30:30Z

166

I don't think I could explain it better than the manual already does:

In choosing which index type to use, GiST or GIN, consider these performance differences:

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 [...]

GIN indexes are two-to-three times larger than GiST indexes

The link and quote refer to the manual for PostgreSQL 9.4. Size and performance estimates seemed slightly outdated already. With PostgreSQL 9.4 the odds have shifted substantially in favor of GIN. The release notes of PostgreSQL 9.4 include:

Reduce GIN index size (Alexander Korotkov, Heikki Linnakangas) [...]

Improve speed of multi-key GIN lookups (Alexander Korotkov, Heikki Linnakangas)

Size and performance estimates have since been removed from the manual.

Note that there are special use cases that require one or the other.

One thing you misunderstood: You never get wrong results with a GiST index. The index operates on hash values, which can lead to false positives in the index. This should only become relevant with a very big number of different words in your documents. False positives are eliminated after re-checking the actual row in any case. The manual:

A GiST index is lossy, meaning that the index may produce false matches, and it is necessary to check the actual table row to eliminate such false matches. (PostgreSQL does this automatically when needed.)

The bold emphasis is mine.

edited Jul 26 at 12:30

Peter Mortensen

31.4k22 gold badges110 silver badges134 bronze badges

answered Mar 11, 2015 at 0:30

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

IamIC Over a year ago

I believe you meant "You never get wrong results with a GIN index", right?

Erwin Brandstetter Over a year ago

@IamIC: You never get wrong results with either GIN or GiST. But I am specifically addressing GiST in the answer because the OP had a wrong impression there.

IamIC Over a year ago

Understood. That makes sense.

Protect children of Donbas2014 Over a year ago

If there are more reads than records in your table, you need to use GIN.

Chandra Agarwala Over a year ago

I think the point GIN indexes are two-to-three times larger than GiST indexes is not valid as I have tested with few large tables and found GiST index taking up more space than GIN and BTree indexes

Collectives™ on Stack Overflow

Difference between GiST and GIN indexes

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related