PostgreSQL的索引

PostgreSQL的索引

一、创建索引
create index 索引名 on 表名 [using method](列名1,列名2,..);



二、删除索引
drop index [concurrently] [if exists] 索引名 [cascade | restrict];

参数解释：
concurrently
在表上删除索引的同时不对查询，插入，更新，删除加锁。 普通的 DROP INDEX 会使表获得独占锁，阻塞其他的访问，直到
索引删除完成。使用此选项, 会由阻塞变为等待，直到其他冲突事物结束。

cascade
级联删除依赖于该索引的对象。

restrict
如果有依赖对象存在，则拒绝删除该索引。这个是缺省。




三、列出索引：
select * from pg_indexes;
截图


命令行：
\d table_name;

pg_indexes.png

四、索引类型：
B-tree索引：
查询列使用到如下表达式时，一般使用B-tree索引：{默认索引均为B-tree索引}
    <
    <=
    =
    >=
    between
    in
    is null
    is not null

    支持如下的模式匹配：
    column_name like 'foo%'
    column_name like 'bar%'
    column_name like ~ '^foo'


hash index：
当查询列只需使用表达式：=  一般使用hash index

gin index：

brin index：

gist index：

sp-gist indexes：




五、唯一索引：
指定唯一性后，索引的列的值必须唯一。如两列或多列时，两列或多列的值共同唯一。
create unique index 索引名 on 表名 (列名1,列名2...);




六、基于表达式的索引：
create index 索引名 on 表名 (表达式);

create index inx_title on film (lower(title));




七、局部索引：
create index 索引名 on 表名 (列名) where condition;

create index inx_title on film (title) where title like 'Cu%';



八、重新索引：
reindex {index | table | schema | database | system} name;

reindex index index_name;

reindex table table_name;

reindex schema schema_name;

reindex database database_name;

reindex system database_name;




九、多列索引：
create index 索引名 on 表名(a,b,c...);

where a=v1 and b=v2 and c=v3; 可以命中索引
where a=v1 and v=v2;          可以命中索引
where a=v1;                   可以命中索引
where c=v3 and b=v2;          有时可以命令有时不能命名索引，视具体环境
where a=v1 or b=v2;           不能命中索引


索引失效的场景：
1、任何计算、函数、类型转换
2、!=
3、NOT，相当于使用函数
4、模糊查询通配符在开头
5、索引字段在表中占比较高
6、多字段btree索引查询条件不包含第一列
7、多字段索引查询条件使用OR（有时也会走索引扫描，但查询效率不高）
8、如果查询的目标表中数据量很少的情况下，PostgreSQL不会走索引查询的，而是直接顺序查找。

最后编辑于：2020.04.22 22:58:45