Skip to main content
added 30 characters in body
Source Link
Dana the Sane
  • 15.2k
  • 8
  • 61
  • 81

Looking at the online docs to refresh my memory, a few things stick out. First, both GIN and GiST indexes are for full text indexing, while b-trees are agnostic. Also, GiST indexes are lossy to an extent, meaning that the DBMS has to deal with false positives/negatives, i.e.:

GiST indexes are lossy because each document is represented in the index by a fixed- length signature. The signature is generated by hashing each word into a random bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct. b-trees do not have this behavior, so depending on the data being indexed, there may be some performance difference between the two.

See for text search behavior http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html and http://www.postgresql.org/docs/8.3/static/indexes-types.html for a general purpose comparison.

Looking at the online docs to refresh my memory, a few things stick out. First, both GIN and GiST indexes are for full text indexing, while b-trees are agnostic. Also, GiST indexes are lossy to an extent, meaning that the DBMS has to deal with false positives/negatives, i.e.:

GiST indexes are lossy because each document is represented in the index by a fixed- length signature. The signature is generated by hashing each word into a random bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct. b-trees do not have this behavior, so depending on the data being indexed, there may be some performance difference between the two.

See http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html

GiST indexes are lossy to an extent, meaning that the DBMS has to deal with false positives/negatives, i.e.:

GiST indexes are lossy because each document is represented in the index by a fixed- length signature. The signature is generated by hashing each word into a random bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct. b-trees do not have this behavior, so depending on the data being indexed, there may be some performance difference between the two.

See for text search behavior http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html and http://www.postgresql.org/docs/8.3/static/indexes-types.html for a general purpose comparison.

Source Link
Dana the Sane
  • 15.2k
  • 8
  • 61
  • 81

Looking at the online docs to refresh my memory, a few things stick out. First, both GIN and GiST indexes are for full text indexing, while b-trees are agnostic. Also, GiST indexes are lossy to an extent, meaning that the DBMS has to deal with false positives/negatives, i.e.:

GiST indexes are lossy because each document is represented in the index by a fixed- length signature. The signature is generated by hashing each word into a random bit in an n-bit string, with all these bits OR-ed together to produce an n-bit document signature. When two words hash to the same bit position there will be a false match. If all words in the query have matches (real or false) then the table row must be retrieved to see if the match is correct. b-trees do not have this behavior, so depending on the data being indexed, there may be some performance difference between the two.

See http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html