Postgresql杂谈 04—Postgresql中的五种常规索引

最新推荐文章于 2026-03-29 04:12:13 发布

原创

最新推荐文章于 2026-03-29 04:12:13 发布 · 3.2k 阅读

标签

#postgresql #gist #gin #数据库

本文详细介绍了PostgreSQL中的五种索引类型，包括B树索引、Hash索引、Gist索引、SP-Gist索引及GIN索引，并演示了各自的创建与使用方法。

一、索引的分类

Postgresql中索引一共分为5种，每一种都有它合适的应用场景，我们在使用时要根据不同业务的特点，选择合适的索引，这样才能加快sql语句的查询效率。下面，我们将就每种不同的索引，介绍其特点。

2.1 B树索引

这是我们最常用的索引结构了，B树是一颗多路平衡查找树，每个节点包含多个键，而且这些键对应的指针一般指向磁盘上同一个数据块，目的是一次从磁盘读取一个数据块，减少磁盘IO操作，加快查询的效率。

B树索引的结构如下所示：

接下来，我们将介绍B树索引的用法。在此之前，先介绍了要用到的数据环境，我们要在一个名为test的表上建立B树索引，表的结构如下：

stock_analysis_data=# \d+ test
                                                Table "public.test"
   Column    |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
-------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 fund_code   | character varying(256)      |           |          |         | extended |              | 
 fund_name   | character varying(256)      |           |          |         | extended |              | 
 record_time | timestamp without time zone |           |          |         | plain    |              |

该表中目前大概有500W+的数据，数据类似如下：

stock_analysis_data=# select * from test limit 10;
 fund_code |        fund_name         |       record_time       
-----------+--------------------------+-------------------------
 160630    | 鹏华中证国防指数分级     | 2020-08-04 05:54:16.313
 001838    | 国投瑞银国家安全混合     | 2020-08-04 05:54:16.313
 160643    | 鹏华空天军工指数(LOF)    | 2020-08-04 05:54:16.313
 002703    | 长城久源灵活配置混合     | 2020-08-04 05:54:16.313
 164402    | 前海开源中航军工         | 2020-08-04 05:54:16.313
 161628    | 融通军工分级             | 2020-08-04 05:54:16.313
 161024    | 富国中证军工指数分级     | 2020-08-04 05:54:16.313
 163115    | 申万菱信中证军工指数分级 | 2020-08-04 05:54:16.313
 003017    | 广发中证军工ETF联接A     | 2020-08-04 05:54:16.313
 005693    | 广发中证军工ETF联接C     | 2020-08-04 05:54:16.313

在建立B树索引之前，我们先用explain查看下查询fund_code为160630的数据执行情况：

        stock_analysis_data=# explain analyze verbose select * from test where fund_code='160630';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..73637.05 rows=1022 width=42) (actual time=0.288..2625.485 rows=1024 loops=1)
   Output: fund_code, fund_name, record_time
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on public.test  (cost=0.00..72534.85 rows=426 width=42) (actual time=16.141..2615.515 rows=341 loops=3)
         Output: fund_code, fund_name, record_time
         Filter: ((test.fund_code)::text = '160630'::text)
         Rows Removed by Filter: 1685163
         Worker 0: actual time=31.975..2614.851 rows=310 loops=1
         Worker 1: actual time=16.427..2612.026 rows=371 loops=1
 Planning Time: 0.070 ms
 Execution Time: 2626.203 ms

整个SQL语句在没有建立索引的情况下，耗时2626ms，扫描出了1024条数据。现在我们就在fund_code字段上建立索引。

stock_analysis_data=# create index mybtindex on test(fu

最低0.47元/天解锁文章