PG索引

創(chuàng)建索引方法參考。
索引是PG訪問方法（AM，Access Method）的一種。PG通過AM來擴(kuò)展數(shù)據(jù)庫(kù)的功能，當(dāng)前支持?jǐn)U展表和索引。
查看當(dāng)前數(shù)據(jù)庫(kù)支持的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;方式安裝索引。
每一種索引都支持不同的操作符操作數(shù)據(jù)類型，擁有不同的屬性。

索引的屬性

創(chuàng)建索引時(shí)，可以根據(jù)索引類型支持的屬性創(chuàng)建不同索引。
如默認(rèn)的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
訪問方法允許我們?cè)趧?chuàng)建索引時(shí)指定值的排列順序（目前只有 btree 支持）。
如CREATE INDEX index_1 ON table_1 (col1 DESC NULLS FIRST, col2);
can_unique.
支持唯一約束和主鍵（只應(yīng)用于 btree）。
如CREATE UNIQUE INDEX index_1 ON table_1 (col1);
can_multi_col
可以在多列上創(chuàng)建索引。
CREATE INDEX index_multi ON table_1 (col1, col2, col3);
can_exclude
支持排除約束 EXCLUDE。

操作符類和操作符族（Operator classes and families）與操作符、數(shù)據(jù)類型

operator class 包含了索引操作特定數(shù)據(jù)類型的最小操作符集合, operator class 被歸類為 operator family ,也就是或一個(gè)family可能包含多個(gè) operator class 。例如SELECT * FROM users WHERE users.email = 'test@example.com';查詢語句，我們使用操作符為=，查詢條件的數(shù)據(jù)類型為text。

操作符屬于操作符類，操作符類屬于操作符族。不同類型索引支持的操作符族不同，支持的數(shù)據(jù)類型也不相同。如操作符族integer_ops就可以包含int8_ops、int2_ops 、int4_ops三個(gè)操作符類，分別用于bigint，smallint，integer三種數(shù)據(jù)類型，每個(gè)操作符類又分別支持>,>=,=,<=,<操作符。text_pattern_ops操作符族包含text_pattern_ops，varchar_pattern_ops兩個(gè)操作符類，用于操作text類型數(shù)據(jù)。

每種數(shù)據(jù)類型，可以使用多種操作符族，不指定的話會(huì)使用默認(rèn)操作符族里的默認(rèn)操作符類。如text類型數(shù)據(jù)，默認(rèn)使用text_ops操作符族下的text_ops操作符類，該操作符類不支持LIKE查詢。想要LIKE查詢時(shí)也使用索引，需要在創(chuàng)建見索引時(shí)，指定該列使用text_pattern_ops操作符類。CREATE INDEX index_1 ON users (email text_pattern_ops);但如果只創(chuàng)建了text_opattern_ops操作符類索引，則無法使用>,=,<,>=,<=等操作符，如果需要使用到這些操作符，還需要給該列創(chuàng)建一個(gè)支持該操作符的索引(如text_ops)CREATE INDEX index_2 ON users (email)。

不同索引支持的操作符類查尋方法參考1，參考2。

btree

btree支持所有的數(shù)據(jù)類型，支持排序，支持>,<,=,>=,<=操作符。
還可以通過顯式指定操作符類來支持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索引存儲(chǔ)的時(shí)被索引字段VALUE的哈希值，只支持=操作符。
  CREATE INDEX hash_index ON table_1 USING hash (col1);

gin

• gin使用參考
• 支持多值類型，如數(shù)組、json、全文檢索。
• 支持的操作符如下。
  
  image.png

...
更多索引使用場(chǎng)景參考。

示例--給Json數(shù)據(jù)類型里的value創(chuàng)建索引

• 單key場(chǎng)景，使用btree索引
  CREATE INDEX index_1 ON test using btree (((js->>'key1')::int));
  查詢的時(shí)候可以在查詢語句前加explain來檢查索引是否生效。

explain select * from test where (js->>'key1')::int = 5;
explain select * from test where (js->>'key1')::int between 1 and 5;

• 多key場(chǎng)景，使用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支持只對(duì)部分?jǐn)?shù)據(jù)創(chuàng)建索引，如業(yè)務(wù)上只關(guān)心沒有被刪除的數(shù)據(jù)行，則可以只對(duì)未被刪除的行創(chuàng)建索引。
CREATE INDEX index_1 ON users USING btree (email) WHERE deleted_timestamp = 0;

創(chuàng)建索引時(shí)使用表達(dá)式和函數(shù)

CREATE INDEX index_f ON users USING btree (lower(email));
注：此時(shí)查詢語句為SELECT * FROM users WHERE lower(email) = $1才能使用索引，SELECT * FROM users WHERE email = $1不會(huì)使用索引。

生產(chǎn)環(huán)境創(chuàng)建索引

創(chuàng)建索引時(shí)，會(huì)獲取一個(gè)表級(jí)別的排他鎖，阻止對(duì)該表的所有讀取和寫入。如果在生產(chǎn)環(huán)境創(chuàng)建索引，需要加上CONCURRENTLY關(guān)鍵字。
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;

查看某個(gè)操作符類支持的操作及索引

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