PostgreSQL：GIN还是GiST索引？ [英] PostgreSQL: GIN or GiST indexes?

问题描述

从我能找到的信息中，他们都解决了同样的问题 - 更多深奥的操作，如数组包含和交集（&&，@>，< @等）。但是我会对何时使用其中一个（或两者都不可能）的建议感兴趣。

PostgreSQL文档有一些关于此的信息：

From what information I could find, they both solve the same problems - more esoteric operations like array containment and intersection (&&, @>, <@, etc). However I would be interested in advice about when to use one or the other (or neither possibly).
The PostgreSQL documentation has some information about this:

• GIN索引查找是大约比GiST快三倍


• GIN索引的构建时间比GiST长三倍


• GIN索引的更新速度比GiST慢十倍


• GIN索引比GiST大两到三倍

• GIN index lookups are about three times faster than GiST
• GIN indexes take about three times longer to build than GiST
• GIN indexes are about ten times slower to update than GiST
• GIN indexes are two-to-three times larger than GiST

但是我会是特别有兴趣知道当索引大小比例的内存开始变小时（即索引大小变得比可用内存大得多）时是否存在性能影响？我在#postgresql IRC频道上被告知GIN需要将所有索引保留在内存中，否则它将无效，因为与B-Tree不同，它不知道从磁盘读入哪个部分一个特定的查询？问题是：这是真的（因为我也被告知与此相反）？ GiST是否有相同的限制？在使用其中一种索引算法时，我应该注意其他限制吗？

However I would be particularly interested to know if there is a performance impact when the memory to index size ration starts getting small (ie. the index size becomes much bigger than the available memory)? I've been told on the #postgresql IRC channel that GIN needs to keep all the index in memory, otherwise it won't be effective, because, unlike B-Tree, it doesn't know which part to read in from disk for a particular query? The question would be: is this true (because I've also been told the opposite of this)? Does GiST have the same restrictions? Are there other restrictions I should be aware of while using one of these indexing algorithms?

推荐答案

首先，你需要用它们进行文本搜索索引？ GIN和GiST是某些数据类型的索引。如果你需要索引简单的char或整数值，那么普通的B-Tree索引是最好的。

无论如何，PostgreSQL文档有一章关于 GIST 和 GIN ，您可以在其中找到更多信息。

最后但并非最不重要的是，找到最佳方法的最佳方法是生成样本数据（尽可能多你需要成为一个真实的场景），然后创建一个GIST索引，测量创建索引所需的时间，插入一个新值，执行一个示例查询。然后删除索引并使用GIN索引执行相同操作。比较这些值，您将获得所需的答案，具体取决于您的数据。

First of all, do you need to use them for text search indexing? GIN and GiST are index specialized for some data types. If you need to index simple char or integer values then the normal B-Tree index is the best.
Anyway, PostgreSQL documentation has a chapter on GIST and one on GIN, where you can find more info.
And, last but not least, the best way to find which is best is to generate sample data (as much as you need to be a real scenario) and then create a GIST index, measuring how much time is needed to create the index, insert a new value, execute a sample query. Then drop the index and do the same with a GIN index. Compare the values and you will have the answer you need, based on your data.

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