PostgreSQL 技术之家: postgres 索引

索引是数据库一般的优化手段，以用来提高性能，更快的检索数据，但是索引也会增加整个数据库的开销，存储压力。因此要合理的使用索引，首先则是需要了解索引。

以下是创建的语法：

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]

( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )

[ INCLUDE ( column_name [, ...] ) ]

[ WITH ( storage_parameter = value [, ... ] ) ]

[ TABLESPACE tablespace_name ]

[ WHERE predicate ]

创建索引后，能需要定期的使用 analyze 命令对索引进行分析，以更新统计信息，使执行计划准确。

默认情况下，创建索引是允许对表进行select 查询，但是会堵塞写操作（insert，update，delete）。在创建索引的时候，尽量使用同时允许写的 CONCURRENTLY 选项并行创建索引，但是有几点需要注意

1，必须扫描表两遍，此外必须等待可能修改或使用索引的现有事务终止。因此，此选项会导致更长的创建索引的时间，同时会带来更多的消耗，引起其他操作的缓慢。

2，在并发建立索引的过程中，只有一个事务进入到系统目录中，在另外两个事务进行表的各自一次的扫描，在每次扫描表之前，必须等待已修改表的现有事务的终止。在第二次扫描后，索引创建必须等待具有快照的任何事务在第二次扫描之前终止。然后索引标记为可用，create index 命令完成。但是即使这样，索引也可能无法立即作用于查询，在最坏的情况下，只要在索引构建之前存在事务，就不能使用它。

测试是这样的：

如果存在未提交的事务，那么该索引就会一直等待该DML完成。

索引状态为：INVALID

3，如果在扫描表的时候，出现问题，类似出现死锁，或唯一索引的唯一约束报错，那么create index 命令会失败，并留下“无效”的索引，该索引将会被忽略，因为不完整，但是他仍然会增加DML消耗。解决这种问题的办法是删除该索引，并重新创建，这种情况下使用rebuild 也可以，但是rebuild 不支持concurrently 选项，意义不大。

4，在创建唯一索引，执行第二次扫描的时候，已经开始对其他事务强制执行唯一性约束，这意味着，在索引可使用前，甚至在索引最终创建失败的情况下，都会引发唯一约束问题。

5，支持并发创建表达式索引和部分索引，但是与唯一索引类似，可能会出现错误。

6，一般索引的创建，可以支持几个常规索引同时创建，但是并发索引的创建，同一时间，只能有一个。另一个区别是可以在事务块中执行常规CREATE INDEX命令，但CREATE INDEX CONCURRENTLY不能。也就是说，可以在autocommit off 的情况下，创建完索引，然后进行rollback,而CREATE INDEX CONCURRENTLY 无法进行rollback

pg 支持的索引类型有： B-tree, Hash, GiST, SP-GiST, GIN and BRIN，不同的索引类型对应不同的算法以适应不同的查询，默认情况下使用btree 索引。

btree索引：可以支持范围，等值的查询。特别是存在： >,< ,>=,<= ,= 的情况下，pg 会使用btree 索引。与这些运算符等效的（between,in）也会使用btree，is null,is not null 也可以与btree 一起使用。也可以用来处理类似like 的查询，一般为右模糊，abcd% ，而左模糊则需要使用反序索引。在非C语言环境的时候，需要使用pattern ops

比如：create index test_idx1 on test(info text_pattern_ops);

btree 也可以用于按顺序检索数据。

hash 索引，只能处理简单的相等比较。只要使用 = 符号，优化器就会考虑使用哈希索引。

GIST 索引不是一种索引，而是一种可以实现很多不同索引策略的基础结构，因此，可以使用GIST索引的特定运算符根据索引策略而变化。例如：pg 包括的几个二维几何数据类型的GIST运算符类，他们支持这些运算符的索引查询。：

<<|

&<|

|&>

|>>

GIST索引，可以优化近邻搜索。如：

select * from order by position <->point'(101,456)' limit 10;

SP-GIST索引，提供支持各种搜索的基础架构，SP-GIST允许实现各种不同的非平衡的基于磁盘的数据结构，例如四叉树，KD树和基数树。PG支持用于二维点的SP-GIST运算符：

GIN索引是“反向索引”,适用于包含多个组件值的数据值，比如数组，反向索引包含每个组件值的单独条目，并且可以有效的处理测试特定组件值是否存在的查询。

与GIST和SP-GIST一样，GIN可以支持许多不同的用户定义索引策略，并且可以使用GIN索引的特定运算符根据索引策略而有所不同。支持以下运算符：

BRIN索引（block range index ）存储关于存储在表的连续物理块范围中的值的摘要，与GIST,SP-GIST,GIN一样，BRIN可以支持许多不同的索引策略，并且可以使用BRIN索引的特定运算符根据索引策略而变化，对于具有线性排序顺序的数据类型，索引数据对应于每个块范围的列中值的最大值和最小值。这支持使用这些运算符的索引查询。

目前只有Btree 索引可以创建唯一索引，在为表定义唯一约束或者主键的时候，自动创建唯一索引。目前Btree,GIST,GIN,BRIN索引都支持多列索引，最多32列，在创建pg的时候可以修改此限制。（通过 pg_config_manual.h）

pg 同样支持表达式索引。

部分索引：

这个地方我觉得更应该说是分区索引。局部索引是为一个个表的子集创建的索引，子集由表达式定义。索引仅仅包含满足谓词的表行的条目，局部索引是一个特别的功能，在以下几种情况下很有用：

1，设置局部索引，以排除常见值。

CREATE INDEX access_log_client_ip_ix ON access_log（client_ip）

WHERE NOT（client_ip> inet'192.168.100.0'AND

client_ip <inet'192.168.100.255'）

2，设置局部索引，以排除不太需要查询的值

CREATE INDEX orders_unbilled_index ON order（order_nr）

WHERE billed is null

3,设置部分唯一索引。

CREATE UNIQUE INDEX tests_success_constraint ON test（object，target）

WHERE status=succeful;

我突然想到，可不可以用来做模糊查询呢？

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from test001 where c1 like '%中国人%';

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------

Bitmap Heap Scan on public.test001 (cost=4.13..41.44 rows=10 width=61) (actual time=0.025..0.026 rows=1 loops=1)

Output: c1

Recheck Cond: (test001.c1 ~~ '%中国人%'::text)

Heap Blocks: exact=1

Buffers: shared hit=2

-> Bitmap Index Scan on test_idx_test001 (cost=0.00..4.13 rows=10 width=0) (actual time=0.013..0.013 rows=1 loops=1)

Buffers: shared hit=1

Planning Time: 0.554 ms

Execution Time: 0.075 ms

哈哈，可以是可以，不过，范围比较少。

运算符类：

一般默认情况下的运算符类已经可以满足要求。

SELECT am.amname AS index_method，

opc.opcname AS opclass_name，

opf.opfname AS opfamily_name，

opc.opcintype :: regtype AS indexed_type，

opc.opcdefault AS is_default

from pg_am am，pg_opclass opc，pg_opfamily opf

where opc.opcmethod = am.oid AND

opc.opcfamily = opf.oid

ORDER BY index_method，opclass_name;

索引以及排序规则 COLLATE

每个索引只能支持一个排序规则，如果需要多个排序规则，则需要多个索引。

pg 也支持仅索引扫描。

有两种索引可以用于加速全文搜索，索引对于全文搜索不是必须的，但是在定期搜索的情况下，通常需要索引。

CREATE INDEX name ON table USING GIN (column);

创建基于GIN（广义倒置索引）的索引。在column必须的tsvector类型。

CREATE INDEX name ON table USING GIST (column);

创建基于GiST（通用搜索树）的索引。在column可以是tsvector或tsquery键入。

GIN索引是首选的文本搜索索引类型。作为反向索引，它们包含每个单词（lexeme）的索引条目，以及匹配位置的压缩列表。多字搜索可以找到第一个匹配项，然后使用索引删除缺少其他单词的行。GIN索引仅存储值的单词（lexemes）tsvector，而不存储其权重标签。因此，在使用涉及权重的查询时，需要重新检查表行。

GiST索引是有损的，这意味着索引可能产生错误匹配，并且有必要检查实际的表行以消除这种错误匹配。（PostgreSQL在需要时自动执行此操作。）GiST索引是有损的，因为每个文档在索引中由固定长度签名表示。通过将每个字散列为n比特串中的单个比特来生成签名，所有这些比特或者一起产生n比特文档签名。当两个单词散列到相同的位位置时，将存在错误匹配。如果查询中的所有单词都匹配（真或假），则必须检索表行以查看匹配是否正确。

请注意，通常可以通过增加maintenance_work_mem来提高GIN索引构建时间，而GiST索引构建时间对该参数不敏感。

这个地方加上where 条件去建立索引，简直爽的不要不要的啊。。where 条件可以作为分区了。

限制：

PostgreSQL文本搜索功能的当前局限性是：

每个lexeme的长度必须小于2K字节
a tsvector （lexemes +位置）的长度必须小于1兆字节
词位数必须小于2 64
位置值tsvector 必须大于0且不大于16,383
（FOLLOWED BY）运算符中的匹配距离不能超过16,384<N>tsquery
每个lexeme不超过256个职位
a中的节点数（lexemes +运算符） tsquery必须小于32,768

postgres 索引

用户登录