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