postgresql 如何确定在 Postgres 中使用什么类型的索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4326625/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine what type of index to use in Postgres?
提问by Kellenjb
I have a Postgres database that has 2 columns that are not primary keys (nor can be), but are searched on a lot and are compared for equality to 2 columns in other tables.
我有一个 Postgres 数据库，它有 2 列不是主键（也不能是），但被大量搜索并与其他表中的 2 列进行比较。
I believe this is a perfect case for adding an index to my tables. I have never used indexing on a database before so I am trying to learn the proper way of doing this.
我相信这是向我的表添加索引的完美案例。我以前从未在数据库上使用过索引，所以我正在努力学习这样做的正确方法。
I have learned that there are multiple types of indexing I can pick from. How do I determine what method will be the most efficient for my database? Also would the proper method be to create a single index that covers both columns?
我了解到我可以选择多种类型的索引。如何确定哪种方法对我的数据库最有效？此外，正确的方法是创建一个涵盖两列的单一索引吗？
采纳答案by Max
Postgres support B-tree, R-tree, Hash, GiST and GIN indexing types. B-tree indexing is the most common and fits most common scenarios. This is the syntax:
Postgres 支持 B-tree、R-tree、Hash、GiST 和 GIN 索引类型。B-tree 索引是最常见的，适合最常见的场景。这是语法：
CREATE INDEX idex_name ON table_name USING btree(column1, column2);
Here is the createindexdocumentation and here is more info on different indextypesin postgres.
这是createindex文档，这里是有关postgres 中不同索引类型的更多信息。
What type of index you should use depends on what types of operations you want to perform. If you simply want equality checking then hash index is the best. For most common operations(e.g. comparison, pattern matching) B-tree should be used. I have personally never used GiST or GIN indexing. ANY Guru out there?
您应该使用哪种类型的索引取决于您要执行的操作类型。如果您只是想要相等性检查，那么哈希索引是最好的。对于最常见的操作（例如比较、模式匹配），应该使用 B 树。我个人从未使用过 GiST 或 GIN 索引。有上师吗？
The documentation describes all these types. They can help you better than me :)
该文档描述了所有这些类型。他们可以比我更好地帮助你:)
Hope this helps.
希望这可以帮助。
回答by Frank Heikens
Try to understand the queryplanneras well, because this part of PostgreSQL has to work with your indexes. EXPLAIN ANALYZE will be essential to optimise your queries.
尝试理解查询规划器，因为 PostgreSQL 的这一部分必须与您的索引一起使用。EXPLAIN ANALYZE 对优化您的查询至关重要。
