Difference between GiST and GIN index

by

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

Link and quote refer to the manual for Postgres 9.4. Size and performance estimates seemed slightly outdated already. With Postgres 9.4 the odds have shifted substantially in favor of GIN.
The release notes of Postgres 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.)

Bold emphasis mine.

More Related Contents:

  1. PostgreSQL LIKE query performance variations
  2. How does PostgreSQL enforce the UNIQUE constraint / what type of index does it use?
  3. Why does PostgreSQL perform sequential scan on indexed column?
  4. Do nullable columns occupy additional space in PostgreSQL?
  5. Does a Postgres UNIQUE constraint imply an index?
  6. Postgres query optimization (forcing an index scan)
  7. How does PostgreSQL perform ORDER BY with a b-tree index on the field?
  8. Optimize Postgres query on timestamp range
  9. Multicolumn index on 3 fields with heterogenous data types
  10. How to add a conditional unique index on PostgreSQL
  11. PostgreSQL composite primary key
  12. What to do with null values when modeling and normalizing?
  13. Postgres: INSERT if does not exist already
  14. postgres default timezone
  15. How to close idle connections in PostgreSQL automatically?
  16. postgresql – replace all instances of a string within text field
  17. How to create index on JSON field in Postgres?
  18. Is it possible to have autoincrement number without it being an ID?
  19. pgadmin4 : postgresql application server could not be contacted.
  20. PostgreSQL error ‘Could not connect to server: No such file or directory’
  21. psql: FATAL: database “” does not exist
  22. Postgres FOR LOOP
  23. Storing Images in PostgreSQL
  24. Postgres: Distinct but only for one column
  25. In PostgreSQL, how to insert data with COPY command?
  26. How to work with PGpoint for Geolocation using PostgreSQL?
  27. How to delete an enum type value in postgres?
  28. Why unsigned integer is not available in PostgreSQL?
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment