【postgresql初级使用】创建不同索引类型，选择适合数据类型的索引，查看执行计划，评估不同索引路径的性能

索引的使用

​专栏内容：

• postgresql使用入门基础
• 手写数据库toadb
• 并发编程

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

概述

---

在postgresql中内置了丰富的索引类型如btree, hash, gin, gist, sp-gist，还有扩展的bloom等等索引，同时还可以有不同的用途，如主键，外键，唯一性等。

本文分享如何使用不同类型的索引，如创建，查看，删除；同时通过查看执行计划，了解查询状态与索引使用情况，最后通过案例如果分析增加适合自己的索引。

索引

---

在《postgresql 基础入门》专栏中分享了创建默认类型索引的SQL语法，这里主要介绍创建不同类型索引的语法，同时分享查看索引，删除索引的语法。

创建索引

创建索引的语法如下：

create index index_name ON tablename using index_type ( column1 );
1

其中：

• index_name，本次创建的索引名称，长度在64个字符以内；
• ON关键字之后是表名tablename，在此表的某一列上创建索引，指定列名为 column1；
• using关键字指示使用的索引算法类型，可以使用btree,hash等内置的索引类型；

下面我们在product表的上创建hash索引。

表的定义如下：

-- 创建产品表  
CREATE TABLE products (  
    product_id INT PRIMARY KEY,  
    product_name VARCHAR(255) NOT NULL,  
    price DECIMAL(10, 2) NOT NULL,  
    category VARCHAR(255)  
);  
1
2
3
4
5
6
7

可以插入几条数据，也可以使用前面章节《物化视图》提到的方法初始化大量数据来对比。

这里已经有了很多数据，先打开客户端的执行时间统计功能，查询名称wfplpgsbre为的商品。

postgres=# \timing on
Timing is on.
postgres=# select * from products where product_name='wfplpgsbre';
 product_id | product_name | price  | category
------------+--------------+--------+-----------
         44 | wfplpgsbre   | 199.66 | Category4
(1 row)

Time: 4.828 ms
1
2
3
4
5
6
7
8
9

然后在商品名称列上创建hash类型的索引。

postgres=# create index idx_pro_name ON products using hash ( product_name );
CREATE INDEX
Time: 64.740 ms
1
2
3

这里我们对比一下增加索引后的查询效果。

postgres=# select * from products where product_name='wfplpgsbre';
 product_id | product_name | price  | category
------------+--------------+--------+-----------
         44 | wfplpgsbre   | 199.66 | Category4
(1 row)

Time: 0.279 ms
1
2
3
4
5
6
7

可以看到增加索引后，查询明显变快了，效果提升了20倍。

查看索引

在postgresql的命令行客户端上，可以用以下几种方法来查看。

• 使用psql客户端的\di命令

postgres=> \di
                   List of relations
  Schema  |     Name      | Type  |  Owner   |  Table
----------+---------------+-------+----------+----------
 senlleng | idx_pro_name  | index | senllang | products
 senlleng | orders_pkey   | index | senllang | orders
 senlleng | products_pkey | index | senllang | products
(3 rows)

postgres=> \di idx_pro_name
                   List of relations
  Schema  |     Name     | Type  |  Owner   |  Table
----------+--------------+-------+----------+----------
 senlleng | idx_pro_name | index | senllang | products
(1 row)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

\di 命令是display index 缩写，如果不带索引名称，就会查询当前数据库中的所有索引，可以看到有主键索引，还有刚才创建的索引。
如果带索引名称，就会查看该索引的详细信息。

• 另一种是查看表的定义，使用\d命令

postgres=> \d products
                       Table "senlleng.products"
    Column    |          Type          | Collation | Nullable | Default
--------------+------------------------+-----------+----------+---------
 product_id   | integer                |           | not null |
 product_name | character varying(255) |           | not null |
 price        | numeric(10,2)          |           | not null |
 category     | character varying(255) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
    "idx_pro_name" hash (product_name)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
1
2
3
4
5
6
7
8
9
10
11
12
13

在表的详细信息中，会列出表上的索引列表，可以看到products表中，有两个索引，分别是products_pkey和idx_pro_name。

删除索引

删除索引就比较简单，语法如下：

drop index index_name;
1

删除上面创建的索引。

postgres=> drop index idx_pro_name ;
DROP INDEX
Time: 9.890 ms
1
2
3

查看执行计划

---

索引经常用于提升SQL的查询性能，性能的优化是一个综合而复杂的事情，这里介绍一个常用的手段，就是查看执行计划。

执行计划是数据库将输入SQL命令解析之后，根据当前数据表的状态，如表中数据行的多少，被查询的列上是否有索引等等，对每一种路径都会统计其耗时，然后选出一个耗时较少的路径来最终执行，这就是执行计划。

当然生成执行计划的过程很复杂，但是我们可以查看执行计划，来评估执行的效率，决定是否需要增加索引来优化，以及使用那种类型索引更优秀。

查看执行计划，使用命令explain。

在执行SQL前加explain就会打印执行计划，查看一下查询产品的SQL。

postgres=> explain select * from products where product_name='lrqfankmib';
                         QUERY PLAN
------------------------------------------------------------
 Seq Scan on products  (cost=0.00..1992.00 rows=1 width=31)
   Filter: ((product_name)::text = 'lrqfankmib'::text)
(2 rows)

Time: 0.237 ms
1
2
3
4
5
6
7
8

这是索引已经删除的情况下，可以看到执行计划中Seq Scan on products，说明在这个张表上执行顺序扫描，也就是从头遍历查找，要把表整个遍历一次。

在数据量少时，顺序扫描还是可以接受的，在大数据分析场景下，那简直是灾难级的。

下面我们通过案例来看看，如何使用索引。

案例分析

---

顺序查找效率比较低时，我们给常用字段加一个索引。

添加默认索引类型

给产品名增加默认索引类型btree，来看看执行计划和性能的变化情况。

postgres=> create index btree_proname on products using btree (product_name );
CREATE INDEX
Time: 122.786 ms
1
2
3

看一下执行计划。

postgres=> explain select * from products where product_name = 'lrqfankmib';
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Scan using btree_proname on products  (cost=0.42..8.44 rows=1 width=31)
   Index Cond: ((product_name)::text = 'lrqfankmib'::text)
(2 rows)

Time: 0.322 ms
1
2
3
4
5
6
7
8

可以看到用到了我们刚才创建的索引Index Scan using btree_proname on products。

下面实际再执行一遍，看一下耗时的变化。

postgres=> select * from products where product_name = 'lrqfankmib';
 product_id | product_name | price  | category
------------+--------------+--------+-----------
         51 | lrqfankmib   | 254.70 | Category2
(1 row)

Time: 0.242 ms
1
2
3
4
5
6
7

确实快了很多，到1ms以下了。

创建hash索引

按商品名称的特点，一般不会使用比较运算，符合hash算法的特点，我们在商品名上创建hash索引。

postgres=> create index hash_proname on products using hash ( product_name);
CREATE INDEX
Time: 83.966 ms
postgres=> \d products
                       Table "senlleng.products"
    Column    |          Type          | Collation | Nullable | Default
--------------+------------------------+-----------+----------+---------
 product_id   | integer                |           | not null |
 product_name | character varying(255) |           | not null |
 price        | numeric(10,2)          |           | not null |
 category     | character varying(255) |           |          |
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
    "btree_proname" btree (product_name)
    "hash_proname" hash (product_name)
Referenced by:
    TABLE "orders" CONSTRAINT "orders_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(product_id)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

可以看到创建hash索引之后，商品名上有两个索引，也就是说按索引执行的路径就有两种。

再来看一下执行计划吧。

postgres=> explain select * from products where product_name = 'lrqfankmib';
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using hash_proname on products  (cost=0.00..8.02 rows=1 width=31)
   Index Cond: ((product_name)::text = 'lrqfankmib'::text)
(2 rows)

Time: 0.286 ms
1
2
3
4
5
6
7
8

btree与hash索引比较

可以看到执行计划中使用了刚创建的hash索，也就是说经过postgresql 数据库的计算，还是使用hash索引耗时较短。

下面实际执行一下。

postgres=> select * from products where product_name = 'lrqfankmib';
 product_id | product_name | price  | category
------------+--------------+--------+-----------
         51 | lrqfankmib   | 254.70 | Category2
(1 row)

Time: 0.157 ms
1
2
3
4
5
6
7

可以看到，与btree索引差别非常小，在毫秒级，可能多次执行统计会更准确一些。

总结

---

本文介绍了在postgresql 中使用不同类型索引的方法，通过查看SQL的执行计划，来评估查询的性能，一般采用索引查询。当然不同数据类型，要采用对应的索引类型，会取得较好的效果，最后通过在商品名称上创建btree与hash索引，对应字符匹配hash索引更符合一些。

结尾

---

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：study@senllang.onaliyun.com
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！