如何确定Postgres中使用的索引类型？ [英] How to determine what type of index to use in Postgres?

问题描述

我有一个Postgres数据库，其中有2列不是主键（也不是），但是经过大量搜索，并且在其他表中比较相等于2列。

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.

我相信这是为我的表添加索引的完美案例。我之前从未在数据库上使用过索引，因此我正在尝试学习正确的方法。

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?

推荐答案

Postgres支持B树，R树，Hash，GiST和GIN索引类型。 B树索引是最常见的，也是最常见的场景。这是语法：

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:

CREATE INDEX idex_name ON table_name USING btree(column1, column2);

这是 createindex 文档，这里有更多关于不同的信息 indextypes 。

Here is the createindex documentation and here is more info on different indextypes in postgres.

您应该使用哪种类型的索引取决于您希望的操作类型执行。如果您只是想要进行等式检查，那么哈希索引是最好的。对于大多数常见操作（例如比较，模式匹配），应使用B树。我个人从未使用过GiST或GIN索引。那里有什么古鲁？

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?

文档描述了所有这些类型。他们可以比我更好地帮助你：）

The documentation describes all these types. They can help you better than me :)

希望这会有所帮助。

这篇关于如何确定Postgres中使用的索引类型？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！