PG索引
创建索引方法参考。
索引是PG访问方法（AM，Access Method）的一种。PG通过AM来扩展数据库的功能，当前支持扩展表和索引。
查看当前数据库支持的AM。
postgres=# \dA+
List of access methods
Name | Type | Handler | Description
--------+-------+----------------------+----------------------------------------
brin | Index | brinhandler | block range index (BRIN) access method
btree | Index | bthandler | b-tree index access method
gin | Index | ginhandler | GIN index access method
gist | Index | gisthandler | GiST index access method
hash | Index | hashhandler | hash index access method
heap | Table | heap_tableam_handler | heap table access method
spgist | Index | spghandler | SP-GiST index access method
(7 rows)
#或
select * from pg_am; # amtype为t表示table，i表示index
一般支持brin,btree,gin,gist,hash,spgist等索引。也可以通过CREATE EXTENSION IF NOT EXISTS btree_gin;方式安装索引。
每一种索引都支持不同的操作符操作数据类型，拥有不同的属性。
索引的属性
创建索引时，可以根据索引类型支持的属性创建不同索引。
如默认的btree索引。
postgres=# select a.amname, p.name, pg_indexam_has_property(a.oid,p.name)
from pg_am a,
unnest(array['can_order','can_unique','can_multi_col','can_exclude']) p(name)
where a.amname = 'btree'
order by a.amname;
amname | name | pg_indexam_has_property
--------+---------------+-------------------------
btree | can_order | t
btree | can_unique | t
btree | can_multi_col | t
btree | can_exclude | t
(4 rows)
postgres=#
can_order
访问方法允许我们在创建索引时指定值的排列顺序（目前只有 btree 支持）。
如CREATE INDEX index_1 ON table_1 (col1 DESC NULLS FIRST, col2);
can_unique.
支持唯一约束和主键（只应用于 btree）。
如CREATE UNIQUE INDEX index_1 ON table_1 (col1);
can_multi_col
可以在多列上创建索引。
CREATE INDEX index_multi ON table_1 (col1, col2, col3);
can_exclude
支持排除约束 EXCLUDE。
操作符类和操作符族（Operator classes and families）与操作符、数据类型
operator class 包含了索引操作特定数据类型的最小操作符集合, operator class 被归类为 operator family ,也就是或一个family可能包含多个 operator class 。例如SELECT * FROM users WHERE users.email = 'test@example.com';查询语句，我们使用操作符为=，查询条件的数据类型为text。
操作符属于操作符类，操作符类属于操作符族。不同类型索引支持的操作符族不同，支持的数据类型也不相同。如操作符族integer_ops就可以包含int8_ops、int2_ops 、int4_ops三个操作符类，分别用于bigint，smallint，integer三种数据类型，每个操作符类又分别支持>,>=,=,<=,<操作符。text_pattern_ops操作符族包含text_pattern_ops，varchar_pattern_ops两个操作符类，用于操作text类型数据。
每种数据类型，可以使用多种操作符族，不指定的话会使用默认操作符族里的默认操作符类。如text类型数据，默认使用text_ops操作符族下的text_ops操作符类，该操作符类不支持LIKE查询。想要LIKE查询时也使用索引，需要在创建见索引时，指定该列使用text_pattern_ops操作符类。CREATE INDEX index_1 ON users (email text_pattern_ops);但如果只创建了text_opattern_ops操作符类索引，则无法使用>,=,<,>=,<=等操作符，如果需要使用到这些操作符，还需要给该列创建一个支持该操作符的索引(如text_ops)CREATE INDEX index_2 ON users (email)。
btree
btree支持所有的数据类型，支持排序，支持>,<,=,>=,<=操作符。
还可以通过显式指定操作符类来支持LIKE操作。
CREATE INDEX ON users (email text_pattern_ops);
pgaweb=# EXPLAIN SELECT * FROM users WHERE email LIKE 'lukas@%';
QUERY PLAN
--------------------------------------------------------------------------------------------
Index Scan using users_email_idx on users (cost=0.14..8.16 rows=1 width=4463)
Index Cond: (((email)::text ~>=~ 'lukas@'::text) AND ((email)::text ~<~ 'lukasA'::text))
Filter: ((email)::text ~~ 'lukas@%'::text)
(3 rows)
hash
- hash索引存储的时被索引字段VALUE的哈希值，只支持
=操作符。
CREATE INDEX hash_index ON table_1 USING hash (col1);
gin
image.png
...
更多索引使用场景参考。
示例--给Json数据类型里的value创建索引
- 单key场景，使用btree索引
CREATE INDEX index_1 ON test using btree (((js->>'key1')::int));
查询的时候可以在查询语句前加explain来检查索引是否生效。
explain select * from test where (js->>'key1')::int = 5;
explain select * from test where (js->>'key1')::int between 1 and 5;
- 多key场景，使用btree_gin索引
CREATE EXTENSION btree_gin;
CREATE INDEX index_2 ON test using btree_gin (((js->>'key1)::int),((js->>'key2')::int),((js->'key3')::int));
部分索引
PG支持只对部分数据创建索引，如业务上只关心没有被删除的数据行，则可以只对未被删除的行创建索引。
CREATE INDEX index_1 ON users USING btree (email) WHERE deleted_timestamp = 0;
创建索引时使用表达式和函数
CREATE INDEX index_f ON users USING btree (lower(email));
注：此时查询语句为SELECT * FROM users WHERE lower(email) = $1才能使用索引，SELECT * FROM users WHERE email = $1不会使用索引。
生产环境创建索引
创建索引时，会获取一个表级别的排他锁，阻止对该表的所有读取和写入。如果在生产环境创建索引，需要加上CONCURRENTLY关键字。
CREATE INDEX CONCURRENTLY ON users (email) WHERE deleted_at IS NULL;
附录
查看操作符类支持的操作
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
opc.opcname AS opclass_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid
ORDER BY index_method, opfamily_name, opfamily_operator;
查看某个操作符类支持的操作及索引
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
opc.opcname AS opclass_name,
amop.amopopr::regoperator AS opfamily_operator
FROM pg_am am, pg_opfamily opf, pg_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid AND
opc.opcname='text_ops' AND
opf.opfname='text_ops'
ORDER BY index_method, opfamily_name, opfamily_operator;
参考
https://www.modb.pro/db/521199
http://dbaselife.com/doc/1498/
https://www.postgresql.org/docs/current/indexes-opclass.html
网友评论