PostgreSQL 索引

索引可以理解为数据库的“目录”：它用额外的存储空间和写入成本，换取更快的查询、排序、连接和约束检查。

核心原则只有一句：

根据实际查询条件设计索引，而不是看到字段就创建索引。

1. 索引能解决什么问题

假设有一张股票日行情表：

CREATE TABLE market.daily_quote (
    security_id bigint NOT NULL,
    trade_date date NOT NULL,
    open_price numeric(12, 4),
    close_price numeric(12, 4),
    volume bigint,
    change_rate numeric(10, 4),

    PRIMARY KEY (security_id, trade_date)
);

查询某只股票某天的行情：

SELECT *
FROM market.daily_quote
WHERE security_id = 1001
  AND trade_date = DATE '2026-09-14';

由于主键会自动创建索引，PostgreSQL 可以直接定位目标行，而不必扫描整张表。

索引常用于：

WHERE 条件过滤
多表 JOIN
ORDER BY
GROUP BY
MIN()、MAX()
唯一性检查
UPDATE、DELETE 的目标行定位

但索引不是免费的：

占用磁盘空间
INSERT 时要写入索引
修改被索引字段时要更新索引
DELETE 后需要 VACUUM 清理
索引过多可能降低写入性能
可能减少 HOT Update 的使用机会

PostgreSQL 会自动维护索引，也会由优化器判断是否值得使用。PostgreSQL 18：索引简介

2. 创建、查看和删除索引

2.1 创建普通索引

CREATE INDEX idx_daily_quote_trade_date
ON market.daily_quote (trade_date);

未指定索引类型时，默认创建 B-tree 索引。

2.2 避免重复创建

CREATE INDEX IF NOT EXISTS idx_daily_quote_trade_date
ON market.daily_quote (trade_date);

注意：IF NOT EXISTS 只检查同名对象，不会检查已有索引的定义是否等价。

2.3 查看表上的索引

在 psql 中：

\d market.daily_quote

使用系统视图：

SELECT
    indexname,
    indexdef
FROM pg_indexes
WHERE schemaname = 'market'
  AND tablename = 'daily_quote';

2.4 删除索引

DROP INDEX market.idx_daily_quote_trade_date;

生产环境为了减少写阻塞，可以使用：

DROP INDEX CONCURRENTLY market.idx_daily_quote_trade_date;

3. PostgreSQL 的索引类型

类型	适合场景	常见操作
B-tree	等值、范围、排序、日期、数字、字符串	`= < <= > >= BETWEEN ORDER BY`
Hash	纯等值查询	`=`
GIN	JSONB、数组、全文搜索、多值数据	`@> ? && @@`
GiST	几何、范围、距离、最近邻	`&& @> <->`
SP-GiST	四叉树、基数树、空间划分	取决于操作符类
BRIN	超大且物理顺序相关的表	时间、递增编号范围查询

官方完整说明见 PostgreSQL 18：索引类型。

4. B-tree 索引

B-tree 是 PostgreSQL 最常用、默认的索引类型。

CREATE INDEX idx_daily_quote_trade_date
ON market.daily_quote USING btree (trade_date);

USING btree 可以省略：

CREATE INDEX idx_daily_quote_trade_date
ON market.daily_quote (trade_date);

4.1 等值查询

SELECT *
FROM market.daily_quote
WHERE trade_date = DATE '2026-09-14';

4.2 范围查询

SELECT *
FROM market.daily_quote
WHERE trade_date BETWEEN DATE '2026-09-01'
                     AND DATE '2026-09-14';

也支持：

WHERE close_price >= 10
WHERE volume < 1000000
WHERE trade_date IS NULL
WHERE security_id IN (1001, 1002, 1003)

4.3 排序

B-tree 可以直接按索引顺序输出结果，避免额外排序：

SELECT *
FROM market.daily_quote
WHERE security_id = 1001
ORDER BY trade_date DESC
LIMIT 20;

主键索引是：

PRIMARY KEY (security_id, trade_date)

虽然默认是升序，但 PostgreSQL 可以反向扫描索引，因此通常不需要再创建：

-- 一般属于重复索引
CREATE INDEX ...
ON market.daily_quote (security_id, trade_date DESC);

B-tree 对 ORDER BY ... LIMIT 特别有价值，因为数据库可能只读取索引开头的一小段。PostgreSQL 18：索引与排序

5. 联合索引

联合索引包含多个字段：

CREATE INDEX idx_daily_quote_security_date
ON market.daily_quote (security_id, trade_date);

不过这里已经有相同字段的主键索引，因此不需要重复创建。

5.1 最左列原则

对于 B-tree 索引：

(security_id, trade_date)

最适合：

WHERE security_id = 1001

以及：

WHERE security_id = 1001
  AND trade_date >= DATE '2026-01-01'

也适合：

WHERE security_id = 1001
ORDER BY trade_date DESC
LIMIT 20

但对下面的查询通常不理想：

WHERE trade_date = DATE '2026-09-14'

因为查询没有限制第一列 security_id。

如果经常查询“某天全部股票行情”，可以创建反向顺序的索引：

CREATE INDEX idx_daily_quote_date_security
ON market.daily_quote (trade_date, security_id);

这两个索引对应不同访问路径：

索引	主要用途
`(security_id, trade_date)`	查询某只股票的历史行情
`(trade_date, security_id)`	查询某个交易日的全部股票

5.2 字段顺序怎么确定

常见设计顺序是：

高频等值条件
范围条件
排序字段
返回字段放入 INCLUDE

例如：

SELECT trade_date, close_price, volume
FROM market.daily_quote
WHERE security_id = 1001
  AND trade_date >= DATE '2026-01-01'
ORDER BY trade_date DESC;

适合：

CREATE INDEX idx_daily_quote_security_date_cover
ON market.daily_quote (security_id, trade_date)
INCLUDE (close_price, volume);

但字段顺序必须根据完整查询模式决定，不能简单理解成“选择性最高的字段永远放第一”。

5.3 PostgreSQL 18 的 Skip Scan

PostgreSQL 18 的 B-tree 可以在特定情况下对非首列执行 skip scan。

例如存在索引：

CREATE INDEX idx_example
ON example_table (exchange_code, code);

查询：

SELECT *
FROM example_table
WHERE code = '600519';

如果 exchange_code 只有少量不同值，优化器可能分别尝试每个交易所，从而利用第二列 code。

但 skip scan 是否使用取决于统计信息和成本，不能因此忽略联合索引的字段顺序。PostgreSQL 18：多列索引

6. 覆盖索引与 INCLUDE

普通索引保存索引字段和行位置。找到索引记录后，数据库通常还要访问表数据。

如果查询所需字段全部包含在索引中，就有机会使用 Index Only Scan。

CREATE INDEX idx_daily_quote_date_cover
ON market.daily_quote (trade_date, security_id)
INCLUDE (close_price, change_rate, volume);

对应查询：

SELECT security_id, close_price, change_rate, volume
FROM market.daily_quote
WHERE trade_date = DATE '2026-09-14';

其中：

trade_date、security_id 是索引键，可以过滤和排序。
close_price、change_rate、volume 是附带数据。
INCLUDE 字段不参与索引排序和搜索。
唯一索引的唯一性也只约束索引键，不约束 INCLUDE 字段。

注意，Index Only Scan 还依赖可见性映射。频繁更新的数据页仍可能需要访问原表；对于写入完成后基本不再修改的历史日行情，覆盖索引通常更有价值。

不要放入太多或太宽的字段，否则索引膨胀可能抵消收益。PostgreSQL 18：Index-Only Scan

7. 唯一索引

CREATE UNIQUE INDEX uq_security_exchange_code
ON market.security (exchange_code, code);

它保证同一交易所不能出现重复证券代码。

不过业务唯一性通常更适合直接声明为约束：

ALTER TABLE market.security
ADD CONSTRAINT uq_security_exchange_code
UNIQUE (exchange_code, code);

PRIMARY KEY 和 UNIQUE 约束都会自动创建唯一 B-tree 索引，因此不要再手动创建重复索引。

7.1 NULL 的唯一性

默认情况下，唯一索引允许出现多个 NULL。

如果希望把多个 NULL 也视为重复：

CREATE UNIQUE INDEX uq_security_external_code
ON market.security (external_code)
NULLS NOT DISTINCT;

唯一索引只能使用 B-tree。PostgreSQL 18：唯一索引

8. 部分索引

部分索引只保存满足条件的行。

例如，大多数查询只关心正常上市且非 ST 的股票：

CREATE INDEX idx_security_active_industry_code
ON market.security (industry, code)
WHERE is_active IS TRUE
  AND is_st IS FALSE;

对应查询：

SELECT security_id, code, name
FROM market.security
WHERE industry = '半导体'
  AND is_active IS TRUE
  AND is_st IS FALSE;

优势：

索引更小
更新成本更低
热点数据查询更快

查询条件必须能够推出索引条件。下面的查询不一定能使用这个部分索引：

SELECT *
FROM market.security
WHERE industry = '半导体';

因为它没有排除退市和 ST 股票。

8.1 部分唯一索引

例如只要求“未删除用户”的邮箱唯一：

CREATE UNIQUE INDEX uq_active_user_email
ON app_user (lower(email))
WHERE deleted_at IS NULL;

8.2 不要使用动态日期谓词

下面的设计不可行：

-- 错误：current_date 不是不可变表达式
CREATE INDEX ...
ON market.daily_quote (trade_date)
WHERE trade_date >= current_date - 30;

索引表达式和谓词要求使用不可变函数。动态时间窗口通常通过普通索引、分区或定期维护实现。PostgreSQL 18：部分索引

9. 表达式索引

表达式索引保存计算结果，而不是原始字段。

9.1 忽略大小写查询

CREATE INDEX idx_security_name_lower
ON market.security (lower(name));

查询必须使用对应表达式：

SELECT *
FROM market.security
WHERE lower(name) = lower('ABC Technology');

如果写成：

WHERE name = 'ABC Technology'

通常不能利用 lower(name) 索引。

9.2 日期表达式

CREATE INDEX idx_quote_trade_year
ON market.daily_quote (
    extract(year FROM trade_date)
);

适合：

SELECT *
FROM market.daily_quote
WHERE extract(year FROM trade_date) = 2026;

不过日期范围通常更推荐直接写成：

WHERE trade_date >= DATE '2026-01-01'
  AND trade_date <  DATE '2027-01-01'

这样普通 trade_date 索引即可使用，也更通用。

表达式索引会增加写入成本，因为 PostgreSQL 必须计算并维护表达式结果。PostgreSQL 18：表达式索引

10. GIN 索引

GIN 是倒排索引，特别适合一个字段包含多个可搜索元素的情况。

10.1 JSONB 索引

假设证券表有扩展信息：

extra jsonb

创建索引：

CREATE INDEX idx_security_extra_gin
ON market.security
USING gin (extra);

查询：

SELECT *
FROM market.security
WHERE extra @> '{"province": "贵州"}'::jsonb;

默认 jsonb_ops 支持较多操作，包括：

@>：包含
?：是否存在键
?|：是否存在任意键
?&：是否存在全部键
@?、@@：JSONPath

如果主要使用 JSON 包含查询，可以考虑：

CREATE INDEX idx_security_extra_path
ON market.security
USING gin (extra jsonb_path_ops);

jsonb_path_ops 通常更小，对包含查询更有针对性，但不支持所有键存在操作。PostgreSQL 18：JSONB 索引

10.2 数组索引

CREATE TABLE article (
    article_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tags text[]
);

CREATE INDEX idx_article_tags
ON article
USING gin (tags);

查询包含某个标签：

SELECT *
FROM article
WHERE tags @> ARRAY['PostgreSQL'];

查询标签是否有交集：

SELECT *
FROM article
WHERE tags && ARRAY['PostgreSQL', 'TimescaleDB'];

10.3 全文检索

CREATE INDEX idx_security_profile_fts
ON market.security_profile
USING gin (
    to_tsvector(
        'simple',
        coalesce(primary_business, '') || ' ' ||
        coalesce(company_feature, '')
    )
);

查询表达式应保持一致：

SELECT *
FROM market.security_profile
WHERE to_tsvector(
          'simple',
          coalesce(primary_business, '') || ' ' ||
          coalesce(company_feature, '')
      )
      @@ plainto_tsquery('simple', '芯片 算力');

GIN 通常是 PostgreSQL 全文检索的首选索引类型。PostgreSQL 18：全文检索索引

内置 simple 配置不会完成真正的中文分词。中文全文检索通常需要兼容的中文解析扩展，或者在应用侧预先分词。

11. 模糊搜索与 pg_trgm

普通 B-tree 通常不能高效处理前置通配符：

WHERE name LIKE '%科技%'

可以使用 pg_trgm：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

创建 GIN 三元组索引：

CREATE INDEX idx_security_name_trgm
ON market.security
USING gin (name gin_trgm_ops);

查询：

SELECT *
FROM market.security
WHERE name ILIKE '%科技%';

pg_trgm 也支持：

LIKE
ILIKE
正则表达式
字符串相似度查询

中文子串匹配也可能受益，但短关键词效果有限，需要使用实际数据测试。PostgreSQL 18：pg_trgm

12. GiST 和 SP-GiST

GiST 不是某一种固定索引，而是支持不同数据类型和操作符类的索引框架。

常见于：

几何和空间数据
范围类型
最近邻查询
排除约束

例如保存证券交易日期范围：

CREATE TABLE market.trading_period (
    security_id bigint NOT NULL,
    valid_period daterange NOT NULL
);

创建 GiST 索引：

CREATE INDEX idx_trading_period_range
ON market.trading_period
USING gist (valid_period);

查询与指定日期范围有交集的数据：

SELECT *
FROM market.trading_period
WHERE valid_period && daterange(
    DATE '2026-01-01',
    DATE '2026-07-01'
);

SP-GiST 适合四叉树、k-d tree、基数树等非平衡数据结构。普通业务系统使用频率低于 B-tree、GIN 和 GiST。

13. BRIN 索引

BRIN 不记录每一行的精确位置，而是记录连续数据块的摘要。

特别适合：

表非常大
数据按某字段顺序写入
查询主要是范围查询
允许扫描少量额外数据块

例如逐时间写入的分钟行情表：

CREATE TABLE market.minute_quote (
    security_id bigint NOT NULL,
    recorded_at timestamptz NOT NULL,
    price numeric(12, 4),
    volume bigint
);

创建 BRIN：

CREATE INDEX idx_minute_quote_recorded_at_brin
ON market.minute_quote
USING brin (recorded_at);

适合：

SELECT *
FROM market.minute_quote
WHERE recorded_at >= TIMESTAMPTZ '2026-09-14 09:30:00+08'
  AND recorded_at <  TIMESTAMPTZ '2026-09-14 15:00:00+08';

BRIN 的特点：

索引非常小
创建和维护成本低
查询结果需要重新检查原表
数据物理顺序越接近索引字段顺序，效果越好

如果查询模式是“某一只股票最近 100 条行情”，通常还需要 B-tree：

CREATE INDEX idx_minute_quote_security_time
ON market.minute_quote (security_id, recorded_at DESC);

因此 BRIN 和 B-tree 可以并存，服务不同查询。

14. Hash 索引

CREATE INDEX idx_security_code_hash
ON market.security
USING hash (code);

Hash 只支持等值查询：

WHERE code = '600519'

不支持：

WHERE code > '600000'
ORDER BY code

实际业务中 B-tree 同样支持等值查询，而且更通用，所以 Hash 的使用场景相对有限。

15. PostgreSQL 可以组合多个索引

假设有两个独立索引：

CREATE INDEX idx_security_industry
ON market.security (industry);

CREATE INDEX idx_security_board
ON market.security (board);

查询：

SELECT *
FROM market.security
WHERE industry = '半导体'
  AND board = '科创板';

PostgreSQL 可能使用 Bitmap Index Scan 分别读取两个索引，再执行 BitmapAnd。

对于：

WHERE industry = '半导体'
   OR board = '科创板'

则可能使用 BitmapOr。

需要注意：

多索引组合通常不如合适的联合索引直接。
位图扫描会失去索引原有顺序。
如果还有 ORDER BY，可能需要额外排序。
独立索引灵活，联合索引对固定组合查询通常更高效。

因此不能简单地说“一条查询只能使用一个索引”。PostgreSQL 18：组合多个索引

16. 外键字段需要索引吗

PostgreSQL 会自动为：

主键
唯一约束

创建索引，但不会自动为外键引用端创建索引。

例如：

CREATE TABLE market.security_concept (
    security_id bigint NOT NULL
        REFERENCES market.security(security_id),
    concept_id bigint NOT NULL
        REFERENCES market.concept(concept_id),

    PRIMARY KEY (security_id, concept_id)
);

主键索引适合：

WHERE security_id = 1001

但是经常反向查询“某概念有哪些股票”：

SELECT security_id
FROM market.security_concept
WHERE concept_id = 10;

应该增加反向索引：

CREATE INDEX idx_security_concept_concept_security
ON market.security_concept (concept_id, security_id);

外键索引还可以提升：

子表连接
删除或修改父表键时的引用检查
按父对象查询子记录

17. 为什么创建了索引却没有使用

索引存在不代表 PostgreSQL 必须使用它。

17.1 返回数据太多

SELECT *
FROM market.daily_quote
WHERE trade_date >= DATE '2000-01-01';

如果命中表中绝大多数数据，顺序扫描可能更快。

17.2 表太小

几百行的表直接扫描，可能比访问索引再访问表更快。

17.3 查询表达式不匹配

有普通索引：

CREATE INDEX idx_security_code
ON market.security (code);

但查询写成：

WHERE lower(code) = '600519'

普通 code 索引通常不能直接满足 lower(code)。

17.4 隐式或显式类型转换

WHERE security_id::text = '1001'

这会对索引列执行函数转换，可能无法使用原始整数索引。

更合理：

WHERE security_id = 1001

17.5 前置通配符

WHERE name LIKE '%科技%'

普通 B-tree 不适用，应考虑 pg_trgm。

17.6 统计信息不准确

ANALYZE market.daily_quote;

或：

VACUUM (ANALYZE) market.daily_quote;

优化器依靠统计信息估算行数和成本。

17.7 参数化查询与部分索引

部分索引要求优化器能够证明查询条件满足索引谓词。某些参数化条件在制定通用执行计划时无法完成这种证明。

18. 使用 EXPLAIN 验证索引

只看索引定义不够，应检查执行计划。

EXPLAIN
SELECT *
FROM market.daily_quote
WHERE security_id = 1001
ORDER BY trade_date DESC
LIMIT 20;

实际执行并统计耗时：

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM market.daily_quote
WHERE security_id = 1001
ORDER BY trade_date DESC
LIMIT 20;

常见节点：

节点	含义
`Seq Scan`	顺序扫描整张表
`Index Scan`	读取索引后访问表
`Index Only Scan`	尽量只读取索引
`Bitmap Index Scan`	生成匹配行位图
`Bitmap Heap Scan`	根据位图批量访问表
`Sort`	执行了额外排序

重点关注：

actual time
rows
估算行数与实际行数差距
Buffers: shared hit/read
是否出现额外 Sort
Rows Removed by Filter
Index Only Scan 的 Heap Fetches

不要在生产高负载查询上随意执行 EXPLAIN ANALYZE，因为它会真正运行 SQL。

19. 生产环境创建索引

普通创建：

CREATE INDEX idx_daily_quote_date_security
ON market.daily_quote (trade_date, security_id);

创建期间允许查询，但通常会阻塞该表的写入。

在线创建：

CREATE INDEX CONCURRENTLY idx_daily_quote_date_security
ON market.daily_quote (trade_date, security_id);

CONCURRENTLY：

通常不会长时间阻塞正常写入
需要进行更多扫描和等待
创建时间更长
不能放在普通事务块中
失败时可能留下 INVALID 索引

检查无效索引：

SELECT
    n.nspname AS schema_name,
    c.relname AS index_name,
    i.indisvalid,
    i.indisready
FROM pg_index AS i
JOIN pg_class AS c
  ON c.oid = i.indexrelid
JOIN pg_namespace AS n
  ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;

新表、空表、维护窗口中通常直接使用普通 CREATE INDEX；繁忙生产表再考虑 CONCURRENTLY。PostgreSQL 18：CREATE INDEX

20. 监控索引使用情况

SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = 'market'
ORDER BY idx_scan ASC;

查看索引大小：

SELECT
    pg_size_pretty(
        pg_relation_size(
            'market.idx_daily_quote_date_security'::regclass
        )
    );

查看表和全部相关索引的总空间：

SELECT pg_size_pretty(
    pg_total_relation_size('market.daily_quote')
);

不能仅凭 idx_scan = 0 就删除索引，还要考虑：

统计信息是否刚重置
索引是否用于月末或季末任务
是否用于主键、唯一约束
是否用于外键检查
查询是否主要运行在只读副本
功能是否刚上线

21. 股票系统的推荐索引设计

`market.security`

已有：

PRIMARY KEY (security_id);
UNIQUE (exchange_code, code);

按行业查询正常股票：

CREATE INDEX idx_security_active_industry_code
ON market.security (industry, code)
WHERE is_active IS TRUE
  AND is_st IS FALSE;

名称模糊搜索：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX idx_security_name_trgm
ON market.security
USING gin (name gin_trgm_ops);

`market.daily_quote`

已有：

PRIMARY KEY (security_id, trade_date);

它已经支持某只股票的历史行情，不要再创建相同索引。

如果经常查询整个市场某天的数据：

CREATE INDEX idx_daily_quote_date_security
ON market.daily_quote (trade_date, security_id);

如果市场截面查询只读取少数字段：

CREATE INDEX idx_daily_quote_date_cover
ON market.daily_quote (trade_date, security_id)
INCLUDE (close_price, change_rate, volume);

注意后两个索引功能重叠，通常二选一，而不是全部创建。

`market.security_concept`

已有：

PRIMARY KEY (security_id, concept_id);

增加反向查询索引：

CREATE INDEX idx_security_concept_reverse
ON market.security_concept (concept_id, security_id);

超大时序行情表

CREATE INDEX idx_tick_security_time
ON market.tick_quote (security_id, recorded_at DESC);

CREATE INDEX idx_tick_time_brin
ON market.tick_quote
USING brin (recorded_at);

前者服务单只证券查询，后者服务全市场时间范围扫描。

22. 常见索引误区

每个字段都创建索引

会增加空间、写入和维护成本。
给主键或唯一字段重复创建索引

主键和唯一约束已经自动创建索引。
联合索引字段顺序随意

(security_id, trade_date) 和 (trade_date, security_id)服务不同查询。
单独索引低选择性布尔字段
```
CREATE INDEX ON market.security (is_active);
```
如果绝大多数记录都是 true，价值通常有限。部分索引往往更合适。
把所有 SELECT 字段都放进 INCLUDE

覆盖索引过宽会产生显著膨胀。
认为索引一定比顺序扫描快

大比例读取时，顺序扫描往往更高效。
只看执行时间，不看缓存状态和读取量

应结合 EXPLAIN (ANALYZE, BUFFERS) 多次测试。
为不同写法创建大量相似索引

优先规范查询，再判断是否确实需要新索引。

最终设计流程

设计一个索引时依次回答：

最慢的具体 SQL 是什么？
WHERE 中哪些是等值条件？
哪些是范围条件？
是否有 JOIN？
是否有 ORDER BY ... LIMIT？
查询返回多少比例的数据？
已有主键或联合索引能否覆盖？
是否适合部分索引、表达式索引或 INCLUDE？
写入频率能否接受额外索引成本？
EXPLAIN (ANALYZE, BUFFERS) 是否证明它确实有效？

先找到真实慢查询，再设计最少且有效的索引，通常比堆积大量“可能有用”的索引更可靠。