08-PGIndex

Index

常用三种索引

特性	BTree	GIN	Hash
查询类型	等值、范围、排序	包含、存在、全文搜索	仅等值
数据结构	平衡树	倒排索引	哈希表
插入速度	中等	较慢	快
查询速度	快	非常快（特定查询）	极快（等值查询）
索引大小	中等	较大	中等
支持事务	是	是	是（10+）
并发控制	支持	支持	支持
NULL处理	支持	支持	支持
最常用	✓ 默认选择	✓ 多值类型	△ 特定场景

BTree

• 平衡树结构，支持范围查询和排序
• PostgreSQL 的默认索引类型
• 支持 =, <, <=, >, >=, BETWEEN, IN, IS NULL, IS NOT NULL

GIN

• 通用倒排索引，适用于多值类型
• 支持全文搜索、数组、JSON、范围类型
• 查询快，但插入和更新较慢
• 索引体积较大

Hash

• 哈希表结构，只支持等值查询 (=)
• 查询速度非常快（理论上 O(1)）
• 不支持范围查询、排序
• 在 PostgreSQL 10+ 支持 WAL 日志（崩溃安全）
• 通常不如 BTree 实用

简而言之，范围查询用BTree，数据，JSON用GIN，等值用Hash.

先创建一个有300w行数据的表，方便后面测试。

\help create view;
Command:     CREATE VIEW
Description: define a new view
Syntax:
CREATE [ OR REPLACE ] [ TEMP | TEMPORARY ] [ RECURSIVE ] VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]

-- 创建表
create table table_index(
        id bigserial primary key,
    name varchar(64),
    phone varchar(64)[]
);

-- 使用plsql插入300w行数据
do                   
$$                     
declare
        i int := 0;
begin              
        while i < 3000000 loop
                insert into table_index
                (name,phone) 
                values
                (md5(random()::text || current_timestamp::text)::uuid,array[random()::varchar(64),random()::varchar(64)]);
                i = i + 1;
        end loop;
end;             
$$ language plpgsql;

BTree索引

我们先测试没有添加任何索引的查询

postgres=# explain select * from table_index where id = 222222;
                                      QUERY PLAN                        
              
------------------------------------------------------------------------
--------------
 Index Scan using table_index_pkey on table_index  (cost=0.43..8.45 rows
=1 width=113)
   Index Cond: (id = 222222)
(2 rows)


postgres=# explain select * from table_index  where name = 'd08adb6b-3f76-7baf-5118-cbbd62bfab63';
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Gather  (cost=1000.00..141342.41 rows=1 width=113)
   Workers Planned: 2
   ->  Parallel Seq Scan on table_index  (cost=0.00..140342.31 rows=1 width=113)
         Filter: ((name)::text = 'd08adb6b-3f76-7baf-5118-cbbd62bfab63'::text)
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

可以看到，使用id等值查询走的是主键索引，而name的等值查询走的是全表扫描(Seq Scan)。

接下来创建name字段的索引

postgres=# create index table_index_name on table_index(name);
CREATE INDEX

这时候我们再次进行查询，发现走的是索引

postgres=# explain select * from table_index  where name = 'd08adb6b-3f76-7baf-5118-cbbd62bfab63';
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Index Scan using table_index_name on table_index  (cost=0.56..8.57 rows=1 width=113)
   Index Cond: ((name)::text = 'd08adb6b-3f76-7baf-5118-cbbd62bfab63'::text)
(2 rows)

GIN索引

postgres=# select * from table_index where phone @> array['0.4447699815441366'::varchar(64)];

   id   |                 name                 |                  phone                  
--------+--------------------------------------+-----------------------------------------
 222222 | d08adb6b-3f76-7baf-5118-cbbd62bfab63 | {0.4447699815441366,0.9998937575336129}
(1 row)

postgres=# explain select * from table_index where phone @> array['0.4447699815441366'::varchar(64)];
                                     QUERY PLAN                                      
-------------------------------------------------------------------------------------
 Gather  (cost=1000.00..144342.00 rows=30000 width=113)
   Workers Planned: 2
   ->  Parallel Seq Scan on table_index  (cost=0.00..140342.00 rows=12500 width=113)
         Filter: (phone @> '{0.4447699815441366}'::character varying(64)[])
 JIT:
   Functions: 2
   Options: Inlining false, Optimization false, Expressions true, Deforming true
(7 rows)

我们发现走的是全表扫描，接下来我们针对数组类型创建GIN索引(时间可能较长)。

create index table_index_phone_gin on table_index using gin(phone);
CREATE INDEX

这时候我们再次查询，发现走的是gin索引（Bitmap Heap Scan）

postgres=# explain select * from table_index where phone @> array['0.4447699815441366'::varchar(64)];
                                        QUERY PLAN                                        
------------------------------------------------------------------------------------------
 Bitmap Heap Scan on table_index  (cost=228.62..67196.11 rows=30000 width=113)
   Recheck Cond: (phone @> '{0.4447699815441366}'::character varying(64)[])
   ->  Bitmap Index Scan on table_index_phone_gin  (cost=0.00..221.12 rows=30000 width=0)
         Index Cond: (phone @> '{0.4447699815441366}'::character varying(64)[])
(4 rows)

Hash索引