索引

前言

postgres默认的索引类型是B树。

查看表中索引

psql使用\d <table_name>可以查看索引。

也可以查询 pg_indexes 系统视图

SELECT * FROM pg_indexes WHERE tablename = 'your_table_name';

索引类型

• B树索引。最基本的索引类型
• 唯一索引。
• 组合索引
• 块范围索引（Block Range Index, BRIN）
• 通用倒排索引（Generalized Inverted Index），适用于数组、JSON、全文搜索等
• 通用搜索树（Generalized Search Tree, GiST），适用于几何数据、全文搜索等
• 哈希索引，适用于等值查询

创建索引

• 创建普通B树索引

CREATE INDEX index_name ON table_name (column_name);

• 创建唯一索引

CREATE UNIQUE INDEX index_name ON table_name (column_name);

• 在多个列上创建组合索引

CREATE INDEX index_name ON table_name (column1, column2);

• 创建通用倒排索引

CREATE INDEX products_tags_gin_idx ON products USING GIN (tags);

• 创建GiST索引

CREATE INDEX index_name ON table_name USING GIST (column_name);

• 创建哈希索引

CREATE INDEX index_name ON table_name USING HASH (column_name);

删除索引

DROP INDEX index_name;

如果有些索引长期未被使用，那么这些索引就应该删除。通过pg_stat_user_indexes统计视图可以查询最近索引使用情况。

select relname, indexrelname, idx_scan from pg_catalog.pg_stat_user_indexes;

下面检查无效的索引

select indexrelid, indisvalid from pg_index where indisvalid = 'f';

修改索引

• 重命名索引

ALTER INDEX old_index_name RENAME TO new_index_name;

重建索引

当索引变得碎片化或损坏时，可以使用 REINDEX 命令重建索引。

• 重建单个索引

REINDEX INDEX index_name;

• 重建表中所有索引

REINDEX TABLE table_name;

• 重建数据库中所有索引

REINDEX DATABASE database_name;

• 重建系统索引

REINDEX SYSTEM database_name;

• 并发重建索引（不完全阻塞）

REINDEX CONCURRENTLY INDEX index_name;