PostgreSQL Index

B-tree, Hash, GiST, SP-GiST, GIN, BRIN
默认 B-tree
B-tree
- 相等和范围上下限 <,<=,=,>=,>, BETWEEN, IN, IS NULL, IS NOT NULL
- 前缀匹配 LIKE 'foo%', ~ '^foo', ILIKE, ~* 且大小写处理逻辑相同
HASH - = 操作 - 如果存在 HASH 索引，会优先使用
- 如果确定只会用到 = 建议使用 HASH
- 占用空间比 B-tree 少，速度更快
- 不受值长度影响
GiST
- 支持二维数据 - <<,&<,&>,>>,<<|,&<|,|&>,|>>,@>,<@,~=,&&
- 支持 nearest-neighbor/临近搜索 - location <-> point '(101,456)'
SP-GiST - Space partitioned GiST
- non-balanced disk-based data structures
  - quadtrees, k-d trees, and radix trees (tries)
- 支持二维点 - <<,>>,~=,<@,<^,>^
- 支持 nearest-neighbor
GIN - inverted indexes
- 逆向索引 - 适用于类似数组这样的场景
- <@,@>,=,&&
BRIN - Block Range INdexes
- store summaries about the values stored in consecutive physical block ranges
- 适用于连续相邻数据
- <,<=,=,>=,>
Bloom - CREATE EXTENSION bloom;
索引类型
- 部分索引
- 表达式索引
- 唯一索引
- 多列索引
pg_qualstats - An extension collecting statistics about quals
- quals -> predicates
hypopg - Hypothetical indexes for PostgreSQL
- 评估索引和 Query Plan

Bitmap Index Scan
Bitmap Heap Scan
Index Scan

tip

从旧版本 pg_upgrade 升级后需要 REINDEX 才会利用到新的索引特性
VACUUM FULL 会重建索引
PostgreSQL 13 BTree 索引会去重 - 减少磁盘和内存空间
- 如果希望旧版本行为，可以 deduplicate_items=off
null 不想等 - 因此不会算在 unique

caution

UNIQUE INDEX 只能使用 B-tree
排序只能 B-tree
排序隐含 NULLS LAST, 默认 ASC NULLS LAST - 因此默认 DESC NULLS LAST 不会用到索引

索引支持的操作
SELECT am.amname              AS index_method,
       opc.opcname            AS opclass_name,
       opc.opcintype::regtype AS indexed_type,
       opc.opcdefault         AS is_default
FROM pg_am am,
     pg_opclass opc
WHERE opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;

-- Hash 索引
CREATE INDEX name ON table USING HASH (column);
-- 如果想要 Hash unique 可以使用 constraint
-- 但 ON CONFLICT 需要使用 constraint 名字，pg 不能自己推导
alter table tab add constraint cst_exc_id exclude using hash (id with =);

-- 内置 Hash 函数
SELECT hashtext('text'),
       hashchar('c'),
       hash_array(array [1,2,3]),
       jsonb_hash('{
         "me": "haki"
       }'::jsonb),
       timestamp_hash(now()::timestamp);

-- 临近搜索
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
-- 索引支持 ORDER 和 NULL 顺序
CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);

-- 索引添加额外值 - 可以让常用查询只需要走索引
CREATE INDEX idx_cust2 ON customer(active) INCLUDE (email);


-- 查找重复索引
  SELECT array_agg(indexname) AS indexes, replace(indexdef, indexname, '') AS defn
    FROM pg_indexes
GROUP BY defn
  HAVING count(*) > 1;

-- 索引使用情况
SELECT relname, indexrelname, idx_scan
FROM   pg_catalog.pg_stat_user_indexes
WHERE  schemaname = 'public';

-- 并行构建索引

SET max_parallel_workers = 32;
SET max_parallel_maintenance_workers = 16;

CREATE INDEX CONCURRENTLY idx_address1 ON address(district);

hypopg

SELECT * FROM hypopg_create_index('CREATE INDEX ON test (id)');

SELECT * FROM hypopg_list_indexes();
EXPLAIN SELECT * FROM test WHERE id = 1;

PostgreSQL Index

hypopg

FAQ

access method "hash" does not support unique indexes

重复值索引

hypopg​

FAQ

access method "hash" does not support unique indexes​

重复值索引​

hypopg

access method "hash" does not support unique indexes

重复值索引