GiST和GIN索引的区别 [英] Difference between GiST and GIN index

问题描述

我正在实现一个表，其中有一列的数据类型为 tsvector，我想了解使用哪种索引会更好?

I am implementing a table that has a column with a data type of tsvector and I am trying to understand what index would be better to use?

GIN 还是 GiST?

GIN or GiST?

在查看 这里的 postgres 文档时，我似乎明白了:

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

• GiST 更新和构建索引的速度更快，但准确性不如 gin.

• GiST is faster to update and build the index and less accurate than gin.

GIN 更新和构建索引的速度较慢，但​​更准确.

GIN is slower to update and build the index but is more accurate.

好的，那么为什么有人想要一个 gist 索引字段而不是 gin 呢?如果要点可以给你错误的结果?在这方面肯定有一些优势(外部性能).

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.

有人可以通俗地解释一下我什么时候想使用 GIN 还是 GiST?

Can anybody explain in layman's terms when I would want to use GIN vs. GiST?

推荐答案

我认为没有比 手册 已经做了:

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

在选择使用哪种索引类型时，GiST 或 GIN，请考虑这些性能差异:

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

• GIN 索引查找速度大约是 GiST 的三倍

• GIN index lookups are about three times faster than GiST

GIN 索引的构建时间大约是 GiST 的三倍

GIN indexes take about three times longer to build than GiST

GIN 索引的更新速度比 GiST 索引稍慢，但如果禁用快速更新支持，速度会慢 10 倍左右 [...]

GIN indexes are moderately slower to update than GiST indexes, but about 10 times slower if fast-update support was disabled [...]

GIN 索引比 GiST 索引大两到三倍

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

链接和引用参考 Postgres 9.4 手册.规模和性能估计似乎已经有点过时了.在 Postgres 9.4 中，可能性大大有利于杜松子酒.
Postgres 9.4 的发行说明 包括:

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:

• 减少 GIN 索引大小(Alexander Korotkov、Heikki Linnakangas)[...]

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

提高多键 GIN 查找的速度(Alexander Korotkov、Heikki林纳坎加斯)

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.

您误解了一件事:您永远使用 GiST 索引时不会得到错误的结果.索引对哈希值进行操作，这可能导致索引中出现误报.这应该只与文档中的大量不同单词相关.在任何情况下重新检查实际行后都会消除误报.手册:

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:

GiST 索引是有损的，这意味着该索引可能会产生错误匹配，并且需要检查实际表行以消除此类错误匹配.(PostgreSQL 会在需要时自动执行此操作.)

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

粗体强调我的.

这篇关于GiST和GIN索引的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！