作者
digoal
日期
2019-12-17
标签
PostgreSQL , 索引
背景
PostgreSQL允许在同一列创建多个索引，有什么用？出于什么目的？
create index语法如下
Command: CREATE INDEX
Description: define a new index
Syntax:
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 ]
同一列创建多个索引的作用
1、修复问题
1、膨胀，例如索引由于更新到这了page膨胀，可以通过重建索引来解决性能问题。
pg12开始，直接使用reindex concurrently可以支持
```
Command: REINDEX
Description: rebuild indexes
Syntax:
REINDEX [ ( VERBOSE ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } [ CONCURRENTLY ] name
URL: https://www.postgresql.org/docs/12/sql-reindex.html
```
pg12以前的版本，可以通过create index concurrently创建同样定义的索引，然后删除老的索引。
2、修复索引由于某些原因导致invalid page的问题，也需要重建索引。
2、对同一个字段的查询或约束，在不同场景有不同的需求
1、排序，等值，大于，小于，可以使用btree索引
btree
2、如果这个列还有前后模糊查询，正则表达式查询的需求，需要建立gin索引
gin
```
create extension pg_trgm;
create index idx_t on t1 using gin (info gin_trgm_ops)
select * from t1 where info like '%abcde%';
select * from t1 where info ~ 'abcde';
```
3、空间距离排序查询，数值距离排序查询
gist
```
postgres=# create table t12 (pos point, id int, info text);
CREATE TABLE
postgres=# create index idx_t12_1 on t12 using gist(pos);
CREATE INDEX
postgres=# explain select * from t12 order by pos <-> point(1,100) limit 1;
QUERY PLAN
Limit (cost=0.14..0.18 rows=1 width=60)
-> Index Scan using idx_t12_1 on t12 (cost=0.14..33.74 rows=1020 width=60)
Order By: (pos <-> '(1,100)'::point)
(3 rows)
```
4、like 带前缀或后缀的查询
ops
text_pattern_ops (collate <>'c' like 查询)
```
dtstest=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+------------+----------+---------+------------+---------------------------
dtstest | dtstest | UTF8 | C | en_US.utf8 |
postgres | pg19792171 | UTF8 | C | en_US.utf8 |
template0 | pg19792171 | UTF8 | C | en_US.utf8 | =c/pg19792171 +
| | | | | pg19792171=CTc/pg19792171
template1 | pg19792171 | UTF8 | C | en_US.utf8 | =c/pg19792171 +
| | | | | pg19792171=CTc/pg19792171
(4 rows)
dtstest=> create table test(id int, info text);
CREATE TABLE
dtstest=> create index idx_test_1 on test (info);
CREATE INDEX
dtstest=> explain select * from test where info like 'a%';
QUERY PLAN
Bitmap Heap Scan on test (cost=1.31..6.54 rows=6 width=36)
Filter: (info ~~ 'a%'::text)
-> Bitmap Index Scan on idx_test_1 (cost=0.00..1.31 rows=6 width=0)
Index Cond: ((info >= 'a'::text) AND (info < 'b'::text))
(4 rows)
dtstest=> explain select * from test where info ~ '^a';
QUERY PLAN
Bitmap Heap Scan on test (cost=1.31..6.54 rows=6 width=36)
Filter: (info ~ '^a'::text)
-> Bitmap Index Scan on idx_test_1 (cost=0.00..1.31 rows=6 width=0)
Index Cond: ((info >= 'a'::text) AND (info < 'b'::text))
(4 rows)
```
```
dtstest=> create database db1 with template template0 encoding 'utf8' lc_collate 'en_US.UTF8';
CREATE DATABASE
dtstest=> \c db1
You are now connected to database "db1" as user "dtstest".
db1=> create table test(id int, info text);
CREATE TABLE
db1=> create index idx_test_1 on test (info);
CREATE INDEX
db1=> explain select * from test where info like 'a%';
QUERY PLAN
Seq Scan on test (cost=0.00..25.88 rows=6 width=36)
Filter: (info ~~ 'a%'::text)
(2 rows)
db1=> explain select * from test where info ~ '^a';
QUERY PLAN
Seq Scan on test (cost=0.00..25.88 rows=6 width=36)
Filter: (info ~ '^a'::text)
(2 rows)
db1=> create index idx_test_2 on test (info text_pattern_ops);
CREATE INDEX
db1=> explain select * from test where info like 'a%';
QUERY PLAN
Bitmap Heap Scan on test (cost=1.31..6.54 rows=6 width=36)
Filter: (info ~~ 'a%'::text)
-> Bitmap Index Scan on idx_test_2 (cost=0.00..1.31 rows=6 width=0)
Index Cond: ((info ~>=~ 'a'::text) AND (info ~<~ 'b'::text))
(4 rows)
db1=> explain select * from test where info ~ '^a';
QUERY PLAN
Bitmap Heap Scan on test (cost=1.31..6.54 rows=6 width=36)
Filter: (info ~ '^a'::text)
-> Bitmap Index Scan on idx_test_2 (cost=0.00..1.31 rows=6 width=0)
Index Cond: ((info ~>=~ 'a'::text) AND (info ~<~ 'b'::text))
(4 rows)
```
5、null前后，字段倒序，正序
```
db1=> create index idx1 on test (info nulls first);
CREATE INDEX
db1=> create index idx2 on test (info desc);
CREATE INDEX
```
6、分区（partial index）
```
db1=> create index idx3 on test (info) where abs(mod(id,4))=0;
CREATE INDEX
db1=> create index idx4 on test (info) where abs(mod(id,4))=1;
CREATE INDEX
db1=> create index idx5 on test (info) where abs(mod(id,4))=2;
CREATE INDEX
db1=> create index idx6 on test (info) where abs(mod(id,4))=3;
CREATE INDEX
```
7、全局索引(未来)
create global index ... on partent ...
8、排他约束索引
例如不允许表里面的gis多边形记录存在空间交错 ，或者不允许范围类型交错。
```
db1=> create table t2(id int, c1 box);
CREATE TABLE
db1=> alter table t2 add constraint uk exclude using gist (c1 with &&);
ALTER TABLE
db1=> insert into t2 values (1, box(point(0,0), point(1,1)));
INSERT 0 1
db1=> insert into t2 values (1, box(point(0,0), point(1,1)));
ERROR: conflicting key value violates exclusion constraint "uk"
DETAIL: Key (c1)=((1,1),(0,0)) conflicts with existing key (c1)=((1,1),(0,0)).
db1=> insert into t2 values (1, box(point(0.5,0.5), point(0.6,0.6)));
ERROR: conflicting key value violates exclusion constraint "uk"
DETAIL: Key (c1)=((0.6,0.6),(0.5,0.5)) conflicts with existing key (c1)=((1,1),(0,0)).
```
9、表达式索引
create index idx on t (lower(id));
10、数据在某些字段上的数据分布有比较好的线性相关性
范围查询，可以使用brin，
create index idx on t using brin(ts);
11、使用include index，有不同的include。
``` create index idx1 on t (c1) include (c2,c3,c4);
create index idx2 on t (c1) include (c5,c6); ```
参考
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
