PostgreSQL的索引

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



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

參數(shù)解釋：
concurrently
在表上刪除索引的同時不對查詢，插入，更新，刪除加鎖。 普通的 DROP INDEX 會使表獲得獨(dú)占鎖，阻塞其他的訪問，直到
索引刪除完成。使用此選項(xiàng), 會由阻塞變?yōu)榈却?，直到其他沖突事物結(jié)束。

cascade
級聯(lián)刪除依賴于該索引的對象。

restrict
如果有依賴對象存在，則拒絕刪除該索引。這個是缺省。




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


命令行：
\d table_name;

pg_indexes.png

四、索引類型：
B-tree索引：
查詢列使用到如下表達(dá)式時，一般使用B-tree索引：{默認(rèn)索引均為B-tree索引}
    <
    <=
    =
    >=
    between
    in
    is null
    is not null

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


hash index：
當(dāng)查詢列只需使用表達(dá)式：=  一般使用hash index

gin index：

brin index：

gist index：

sp-gist indexes：




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




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

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;          有時可以命令有時不能命名索引，視具體環(huán)境
where a=v1 or b=v2;           不能命中索引


索引失效的場景：
1、任何計算、函數(shù)、類型轉(zhuǎn)換
2、!=
3、NOT，相當(dāng)于使用函數(shù)
4、模糊查詢通配符在開頭
5、索引字段在表中占比較高
6、多字段btree索引查詢條件不包含第一列
7、多字段索引查詢條件使用OR（有時也會走索引掃描，但查詢效率不高）
8、如果查詢的目標(biāo)表中數(shù)據(jù)量很少的情況下，PostgreSQL不會走索引查詢的，而是直接順序查找。