PHP小码农

Appearance

部分索引（Partial Index） ​

一张表里往往只有一小部分行会被反复查询：订单表里 95% 的行早已「已完成」，只有 5% 的「待处理」订单需要被扫描；日志表里只有最近一个月的记录会被检索；软删除的表里只有 deleted_at IS NULL 的行才需要被看见。给整张表建索引，等于为那些永远不会被查的行白白付出存储与写入代价。

部分索引（Partial Index）就是为解决这个问题而生的：在索引定义上挂一个 WHERE 谓词，只有满足谓词的行才会被写进索引。本节讲清它的语法、谓词必须 IMMUTABLE 这条硬性门槛、谓词蕴含（Predicate Implication）这套「什么查询才用得上」的判定规则，以及它在体积、写入开销与条件唯一性上的典型用法。

前置知识

阅读本节前，建议先了解：CREATE INDEX 基础、B-Tree 索引原理、唯一索引与唯一约束

基础概念 ​

索引里只装「满足谓词的行」 ​

普通索引的键是「全表所有行」的列值；部分索引的键只包含谓词为真的那部分行：

text
普通索引：CREATE INDEX ON orders (status)
          索引条目 ← 所有行的 status     （100 万行 → 100 万条）

部分索引：CREATE INDEX ON orders (status) WHERE status = 'pending'
          索引条目 ← 仅 status='pending' 的行（1 万行 → 1 万条）

一句话概括：部分索引是「带过滤条件的索引」。它用体积和写入换来的收益是：更小的索引、更少的 I/O、更高的缓存命中率。

三个关键前提 ​

前提说明违反的后果
谓词必须是 IMMUTABLE相同输入永远得到相同结果，不依赖会话状态建索引直接报错
谓词只能引用本表列不能出现子查询、不能引用别的表、不能用 VOLATILE 函数建索引直接报错
查询必须能被谓词「蕴含」优化器要能证明「查的行一定在索引里」不报错，但静默退化为顺序扫描

第三条是最容易踩的坑，因为它没有任何提示——你以为索引在工作，EXPLAIN 里却是 Seq Scan。

常见的使用场景 ​

场景谓词写法收益
队列 / 任务表，只查未完成WHERE status = 'pending'索引体积降到 5%
软删除表，只查存活行WHERE deleted_at IS NULL避免为已删数据维护索引
条件唯一性（一租户一条主记录）CREATE UNIQUE INDEX ... WHERE is_primary用索引表达业务规则
日志 / 事件表，只查近期数据WHERE created_at >= '2026-01-01'老数据不占索引
稀疏值（枚举里极少出现的值）WHERE level = 'error'只给少数行建索引
部分列有值（如扩展字段）WHERE payload IS NOT NULL跳过大量 NULL

部分索引不是「免费的过滤器」

部分索引只服务于能匹配谓词的查询。同一个列上的其他查询（不满足谓词条件的）完全用不上它，需要另建普通索引。不要以为建了部分索引就覆盖了该列的全部查询。

语法与用法 ​

text
-- 基本形式：在索引定义末尾附加 WHERE 谓词
CREATE [ UNIQUE ] INDEX 索引名 ON 表名 (列列表) WHERE 谓词;

-- 谓词可以引用索引列，也可以引用没被索引的列
CREATE INDEX 索引名 ON 表名 (created_at) WHERE status = 'pending';
CREATE INDEX 索引名 ON 表名 (created_at) WHERE deleted_at IS NULL;

-- 多列 + 部分
CREATE INDEX 索引名 ON 表名 (tenant_id, created_at) WHERE status <> 'done';

-- 部分唯一索引：条件唯一性
CREATE UNIQUE INDEX 索引名 ON 表名 (tenant_id) WHERE is_primary;

-- 部分索引 + 表达式索引（谓词与表达式可以共存）
CREATE INDEX 索引名 ON 表名 (lower(email)) WHERE deleted_at IS NULL;

-- 部分索引 + 覆盖列（PostgreSQL 11+）
CREATE INDEX 索引名 ON 表名 (created_at) INCLUDE (id, amount) WHERE status = 'pending';

-- 在线创建大表的部分索引
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (created_at) WHERE status = 'pending';

最小可运行示例：

sql
DROP TABLE IF EXISTS orders;

CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status     text        NOT NULL,
  amount     numeric(12,2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

部分索引的本质：只给「一部分行」建 B-Tree ​

PostgreSQL 在处理部分索引时做的事很直白：插入 / 更新一行时先算谓词，为真才往这棵 B-Tree 里写一条；查询时先证明「要查的行都在索引里」，才走这棵 B-Tree。

sql
INSERT INTO orders (status, amount)
SELECT
  (ARRAY['done','done','done','done','pending'])[1 + (g % 5)],
  (g % 500)::numeric(12,2)
FROM generate_series(1, 100000) AS g;

ANALYZE orders;
text
INSERT 0 100000
ANALYZE

此时索引里只有 2 万条「待处理」记录，而不是 10 万条：

text
idx_orders_pending 的键 →  行指针（TID）
2026-01-01 10:00:00+08  → (0, 5)
2026-01-01 10:00:05+08  → (0, 10)
...                      （仅 status='pending' 的行）

查询谓词与索引谓词一致时，就能走 Index Scan：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 10;
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Index Scan using idx_orders_pending on orders (actual rows=10 loops=1)
         Index Cond: (created_at IS NOT NULL)
         Filter: (status = 'pending'::text)
(3 rows)

注意计划里的 Index Cond: (created_at IS NOT NULL)——优化器把索引谓词中被查询蕴含的部分当成了扫描边界。

谓词必须 IMMUTABLE，且只能引用本表列 ​

这是建部分索引时最先撞上的一堵墙。谓词里出现 VOLATILE 函数会直接拒绝：

sql
-- 错误！谓词里不能出现任何易变（VOLATILE）的东西
CREATE INDEX idx_bad_1 ON orders (created_at) WHERE random() < 0.1;
CREATE INDEX idx_bad_2 ON orders (id) WHERE nextval('seq_a') > 0;
text
ERROR:  functions in index predicate must be marked IMMUTABLE

同样，谓词也不能引用别的表或写子查询：

sql
-- 错误！谓词不能引用其他表 / 不能有子查询
CREATE INDEX idx_bad_3 ON orders (id) WHERE amount > (SELECT avg(amount) FROM orders);
text
ERROR:  cannot use subquery in index predicate

一个很容易忽略的限制：谓词里不能出现「会话相关」的表达式，比如当前时间：

sql
-- 错误！now() / CURRENT_DATE 是 STABLE，不能作为索引谓词
CREATE INDEX idx_bad_4 ON orders (created_at) WHERE created_at > now() - interval '7 days';
text
ERROR:  functions in index predicate must be marked IMMUTABLE

「最近 7 天」这种滚动窗口做不了部分索引

created_at > now() - interval '7 days' 依赖查询时刻，不可能作为固定的索引谓词。滚动窗口的正确做法是用分区表（按时间分区），而不是部分索引。部分索引的谓词必须是静态不变的。

谓词蕴含（Predicate Implication）：什么时候才用得上 ​

优化器判断能否使用部分索引的依据是：查询条件是否「蕴含」索引谓词（内部函数 predicate_implied_by）。也就是说，要能证明「满足查询条件的行，一定满足索引谓词」。

能命中（查询条件比索引谓词更严格）：

sql
-- 索引谓词：status = 'pending'
-- 查询条件与谓词相同，命中
SELECT * FROM orders WHERE status = 'pending';

-- 查询条件更严格（多了附加条件），也能命中
SELECT * FROM orders WHERE status = 'pending' AND amount > 100;

-- 数值范围：索引谓词 created_at >= '2026-01-01'，查询更严格，命中
CREATE INDEX idx_orders_2026 ON orders (id) WHERE created_at >= '2026-01-01';
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE created_at >= '2026-06-01';

不能命中（查询条件比索引谓词更宽松，或无法比较）：

sql
-- 错误！查询里没有 status 条件，可能包含非 pending 行
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE amount > 100;

-- 错误！查询的 status 集合比谓词更大
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE status IN ('pending','done');

-- 错误！谓词列被包在函数里，语义上无法比较
EXPLAIN (COSTS OFF) SELECT id FROM orders WHERE upper(status) = 'PENDING';

不要靠猜，用 EXPLAIN 验证

部分索引失效不报错，只是默默变成 Seq Scan。任何依赖部分索引的查询，上线前都应当用 EXPLAIN 看一眼计划节点是否为 Index Scan / Bitmap Index Scan。

小表上优化器会觉得顺序扫描更便宜，这属于正常的代价判断，不代表索引写错了。要验证索引本身可用，可以临时关掉顺序扫描：

sql
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id FROM orders WHERE status = 'pending' AND amount > 100;
RESET enable_seqscan;
text
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Index Scan using idx_orders_pending on orders
   Index Cond: (created_at IS NOT NULL)
   Filter: ((amount > '100'::numeric) AND (status = 'pending'::text))
(3 rows)

Index Scan using idx_orders_pending 出现即证明谓词蕴含关系成立。

参数化查询与 generic plan 的陷阱 ​

这是部分索引在生产上最隐蔽的一个坑。如果应用使用预编译语句（prepared statement），并且 PostgreSQL 选择了通用计划（generic plan），那么谓词里的字面量在计划阶段是「未知的」：

sql
PREPARE q1 (text) AS SELECT id FROM orders WHERE status = $1;

-- 前几次执行会为具体参数值生成 custom plan，部分索引可能被用上
EXECUTE q1('pending');

-- 执行次数够多后可能切换到 generic plan，此时 $1 未知，无法证明谓词蕴含
EXPLAIN (COSTS OFF) EXECUTE q1('pending');
text
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on orders
   Filter: (status = $1)
(2 rows)

规避方式有三种，按推荐程度排序：

sql
-- 方案 A（推荐）：让应用把谓词条件「写死」在 SQL 里，而不是当参数传
--   即 SQL 文本本身就是 WHERE status = 'pending' AND ...

-- 方案 B：把部分索引改回普通索引（牺牲体积换稳定命中）
-- 方案 C：调整 plan_cache_mode，强制 custom plan（有解析开销）
PREPARE q2 (text) AS SELECT id FROM orders WHERE status = $1;
SET plan_cache_mode = force_custom_plan;
EXECUTE q2('pending');
RESET plan_cache_mode;

为什么 custom plan 能用

custom plan 在已知道具体参数值的前提下规划，此时 $1 = 'pending' 可以被代入，predicate_implied_by 能够证明蕴含。generic plan 要求「一次计划应对所有参数值」，自然无法为特定谓词背书。

部分唯一索引：条件唯一性 ​

普通唯一索引要求「全表该列组合唯一」；部分唯一索引只要求「满足谓词的行之间唯一」。这是表达业务规则的利器：

sql
DROP TABLE IF EXISTS user_addresses;

CREATE TABLE user_addresses (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   bigint  NOT NULL,
  address   text    NOT NULL,
  is_default boolean NOT NULL DEFAULT false
);

-- 每个用户最多只能有一个默认地址
CREATE UNIQUE INDEX uq_user_default_address
  ON user_addresses (user_id) WHERE is_default;
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX

规则立刻生效，且只约束 is_default = true 的行：

sql
INSERT INTO user_addresses (user_id, address, is_default) VALUES (1, '地址 A', true);
INSERT INTO user_addresses (user_id, address, is_default) VALUES (1, '地址 B', false);
INSERT INTO user_addresses (user_id, address, is_default) VALUES (1, '地址 C', true);
text
INSERT 0 1
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "uq_user_default_address"
DETAIL:  Key (user_id)=(1) already exists.

这条 CREATE UNIQUE INDEX 的第一个参数是列的列表，而不是任何条件。它与部分索引的 WHERE 子句一起工作，形成了一个只对默认地址生效的唯一性约束。

ON CONFLICT 也能以部分唯一索引作为仲裁目标，但推断子句里必须带上谓词：

sql
-- 正确：用 ON CONFLICT 的推断子句指定索引谓词
INSERT INTO user_addresses (user_id, address, is_default) VALUES (1, '地址 D', true)
ON CONFLICT (user_id) WHERE is_default DO UPDATE SET address = EXCLUDED.address;
text
INSERT 0 1

ON CONFLICT 的谓词必须与索引谓词完全一致

ON CONFLICT (user_id) 而不带 WHERE is_default，会匹配不到这个部分唯一索引，报错：

text
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

体积与写入代价的实测对比 ​

部分索引最大的收益就是体积。在同一张订单表上对比全量索引与部分索引：

sql
-- 全量索引：100 万条
CREATE INDEX idx_orders_all_status ON orders (created_at);

-- 部分索引：仅 pending（约占 20%）
CREATE INDEX idx_orders_pending_2 ON orders (created_at) WHERE status = 'pending';

SELECT indexrelid::regclass                         AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
       pg_relation_size(indexrelid)                 AS bytes
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;
text
      index_name       | index_size |  bytes
-----------------------+------------+---------
 idx_orders_all_status | 4880 kB    | 4997120
 idx_orders_pending_2  | 1248 kB    | 1277952
 idx_orders_pending    | 1248 kB    | 1277952
 orders_pkey           | 2216 kB    | 2269184
(4 rows)

部分索引体积约为全量索引的 25%。除了省磁盘，还有三个连带收益：

维度全量索引部分索引
索引体积100%仅谓词命中的行
写入开销每行都要维护不满足谓词的行完全不维护
缓存命中率索引大、易被挤出索引小、更容易常驻 shared_buffers
VACUUM 成本需遍历全部索引条目条目少，清理更快
查询适用面所有查询仅能满足谓词的查询

被排除的行不是「不维护」，而是「根本不进索引」

对不满足谓词的行，PostgreSQL 连一次 B-Tree 插入都不做。这就是部分索引能同时降低写入放大的原因——它不只是省空间，还省 CPU 与 WAL。

部分索引 + 表达式索引：组合用法 ​

部分索引的谓词与表达式索引的表达式可以共存，两者解决的问题不同：

sql
-- 只给「未删除」的账号建大小写不敏感唯一索引
CREATE UNIQUE INDEX uq_accounts_email_alive
  ON accounts (lower(email)) WHERE deleted_at IS NULL;

-- 只给「活跃」用户建用户名前缀索引
CREATE INDEX idx_users_name_prefix
  ON users (lower(name) text_pattern_ops) WHERE is_active;
text
CREATE UNIQUE INDEX
CREATE INDEX

组合后的查询必须同时满足两个条件才能命中：

sql
-- 命中：表达式一致 + 谓词蕴含
EXPLAIN (COSTS OFF)
SELECT id FROM accounts WHERE lower(email) = 'a@example.com' AND deleted_at IS NULL;

-- 错误！少了 deleted_at IS NULL，无法证明谓词蕴含
EXPLAIN (COSTS OFF)
SELECT id FROM accounts WHERE lower(email) = 'a@example.com';
text
                              QUERY PLAN
------------------------------------------------------------------------
 Index Scan using uq_accounts_email_alive on accounts
   Index Cond: (lower(email) = 'a@example.com'::text)
(2 rows)

组合索引的命中门槛是「叠加」的

表达式索引要求「查询表达式等价」，部分索引要求「查询条件蕴含谓词」。二者组合时两个门槛同时生效，任何一条不满足就退化为顺序扫描。这类索引的注释必须写清楚约定的查询形态。

部分索引与约束/外键的限制 ​

部分索引不能被提升为具名的 UNIQUE / PRIMARY KEY 约束：

sql
-- 错误！部分索引不能被提升为约束
CREATE UNIQUE INDEX uq_tmp ON user_addresses (user_id) WHERE is_default;
ALTER TABLE user_addresses ADD CONSTRAINT uq_default UNIQUE USING INDEX uq_tmp;
text
ERROR:  index "uq_tmp" is a partial index
DETAIL:  Cannot create a primary key or unique constraint using such an index.

与约束相关的重要限制汇总如下：

操作部分索引是否可用
ALTER TABLE ... ADD CONSTRAINT ... USING INDEX不可用，报错
ON CONFLICT (列) WHERE 谓词可用（谓词必须与索引一致）
外键引用无法引用部分索引
REPLICA IDENTITY USING INDEX要求非部分、非表达式索引，不可用
作为 information_schema 中的约束不出现（只是索引，不是约束）

因此「条件唯一性」只能靠唯一的语义去保证，数据库层面它不是「约束」。做 Schema 比对的工具往往看不到它，迁移脚本里要单独处理。

元数据与维护：indpred、pg_get_expr、REINDEX ​

用系统表把部分索引识别出来，靠的是 pg_index.indpred 字段：

sql
SELECT c.relname                                AS index_name,
       i.indisunique                            AS is_unique,
       i.indpred IS NOT NULL                    AS is_partial,
       pg_get_expr(i.indpred, i.indrelid)        AS predicate,
       pg_get_indexdef(i.indexrelid)             AS index_def
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'orders'::regclass
ORDER BY c.relname;
text
     index_name      | is_unique | is_partial |       predicate        |              index_def
---------------------+-----------+------------+------------------------+-------------------------------------
 idx_orders_2026     | f         | t          | (created_at >= '2026-01-01 00:00:00+08'::timestamp with time zone) | ...
 idx_orders_pending  | f         | t          | (status = 'pending'::text) | ...
 idx_orders_pending_2| f         | t          | (status = 'pending'::text) | ...
 orders_pkey         | t         | f          |                        | CREATE UNIQUE INDEX orders_pkey ...
(4 rows)

indpred IS NOT NULL 就是「这个索引是部分索引」。pg_get_expr(i.indpred, i.indrelid) 把内部的 pg_node_tree 反解析成可读的 SQL 片段。

日常维护时，部分索引与普通索引一样需要统计信息与重建：

sql
-- 部分索引同样依赖 ANALYZE 提供准确的选择性估计
ANALYZE orders;

-- 使用率与体积巡检
SELECT indexrelid::regclass AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

-- 膨胀后重建（部分索引同样适用）
REINDEX INDEX CONCURRENTLY idx_orders_pending;
text
      index_name       | idx_scan |   size
-----------------------+----------+-----------
 idx_orders_pending    |       12 | 1248 kB
 orders_pkey           |        0 | 2216 kB
 idx_orders_all_status |        0 | 4880 kB
 idx_orders_pending_2  |        0 | 1248 kB
(4 rows)

部分索引没有自己的统计信息

ANALYZE 收集的是整表的列统计（直方图、MCV 等），不是「谓词命中的那部分行」的统计。因此对高度倾斜的数据，优化器对部分索引内部的估算可能不准，必要时可用 ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 提高精度。

实战示例 ​

场景：一张 100 万行的 jobs 任务表，95% 的任务已经完成，业务只关心「待处理 / 处理中」的任务；同时要求每个用户只能有一个 is_primary = true 的主任务。全部用部分索引实现，并用 EXPLAIN 逐项验证。

第 1 步：建表并灌入 100 万行

sql
DROP TABLE IF EXISTS jobs CASCADE;

CREATE TABLE jobs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    integer     NOT NULL,
  status     text        NOT NULL,
  is_primary boolean     NOT NULL DEFAULT false,
  payload    jsonb       NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO jobs (user_id, status, is_primary, payload, created_at)
SELECT
  (g % 20000) + 1,
  (ARRAY['done','done','done','done','done','done','done','done','pending','running'])[1 + (g % 10)],
  false,
  jsonb_build_object('step', g % 50),
  '2026-01-01'::timestamptz + (g || ' seconds')::interval
FROM generate_series(1, 1000000) AS g;

ANALYZE jobs;

第 2 步：建立四个部分索引

sql
-- 需求 1：只给「未完成」的任务建索引，用于按时间取待办
CREATE INDEX idx_jobs_active
  ON jobs (created_at) WHERE status IN ('pending','running');

-- 需求 2：只给「待处理」任务建覆盖索引，避免回表
CREATE INDEX idx_jobs_pending_inc
  ON jobs (created_at) INCLUDE (user_id, payload) WHERE status = 'pending';

-- 需求 3：每个用户最多一个主任务
CREATE UNIQUE INDEX uq_jobs_primary
  ON jobs (user_id) WHERE is_primary;

-- 需求 4：只给有扩展字段的任务建表达式索引
CREATE INDEX idx_jobs_step
  ON jobs (((payload ->> 'step')::int)) WHERE payload ? 'step';

第 3 步：验证「未完成」查询走了部分索引

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, status FROM jobs
WHERE status IN ('pending','running')
ORDER BY created_at
LIMIT 20;
text
                                          QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Scan using idx_jobs_active on jobs (actual rows=20 loops=1)
         Index Cond: (created_at IS NOT NULL)
         Filter: (status = ANY ('{pending,running}'::text[]))
(3 rows)

第 4 步：验证覆盖索引免回表

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT user_id, payload FROM jobs WHERE status = 'pending' ORDER BY created_at LIMIT 50;
text
                                          QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit (actual rows=50 loops=1)
   ->  Index Only Scan using idx_jobs_pending_inc on jobs (actual rows=50 loops=1)
         Index Cond: (created_at IS NOT NULL)
         Heap Fetches: 0
(3 rows)

Index Only Scan + Heap Fetches: 0 说明所有需要的列都从索引里直接取到了，没有回表。

第 5 步：验证条件唯一性真的只约束主任务

sql
UPDATE jobs SET is_primary = true WHERE id = 1;
UPDATE jobs SET is_primary = true WHERE id = 2 AND user_id = 1;
text
UPDATE 1
ERROR:  duplicate key value violates unique constraint "uq_jobs_primary"
DETAIL:  Key (user_id)=(1) already exists.

同一个 user_id 的多行 is_primary = false 完全不受影响，唯一性只在 is_primary = true 的子集里生效。

第 6 步：验证「查询条件比谓词宽松」时索引失效

sql
-- 错误示范：查询里没有 status 条件，无法蕴含 idx_jobs_active 的谓词
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM jobs WHERE created_at > '2026-06-01' LIMIT 20;
text
                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Seq Scan on jobs (actual rows=20 loops=1)
         Filter: (created_at > '2026-06-01 00:00:00+08'::timestamp with time zone)
(3 rows)

100 万行的 Seq Scan——这就是「部分索引只服务于匹配谓词的查询」的具体体现。

第 7 步：对比体积，量化收益

sql
SELECT indexrelid::regclass                         AS index_name,
       pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'jobs'
ORDER BY pg_relation_size(indexrelid) DESC;
text
      index_name       | index_size
-----------------------+------------
 jobs_pkey             | 25 MB
 idx_jobs_active       | 4328 kB
 idx_jobs_step         | 1480 kB
 idx_jobs_pending_inc  | 1040 kB
 uq_jobs_primary       | 336 kB
(5 rows)

四个部分索引合计约 7 MB，而主键索引就有 25 MB。这就是部分索引的实际价格——在评估「要不要建」时，把这张表列出来比凭感觉判断靠谱得多。

第 8 步：清理

sql
DROP TABLE jobs;

注意事项 ​

  • 谓词必须 IMMUTABLE，且只能引用本表列。 now()、CURRENT_DATE、random()、子查询、别的表一律不行。

    sql
    -- 错误！functions in index predicate must be marked IMMUTABLE
CREATE INDEX idx_bad ON orders (created_at) WHERE created_at > now() - interval '7 days';

  • 查询条件必须能「蕴含」谓词，否则不报错、只是静默变成顺序扫描。这是最难排查的一类「索引失效」。

    sql
    -- 索引 WHERE status = 'pending'，下面这条用不上
SELECT id FROM orders WHERE amount > 100;

  • 部分索引不能被提升为主键 / 唯一约束，也不能被外键引用。

    sql
    -- 错误！index "uq_tmp" is a partial index
ALTER TABLE user_addresses ADD CONSTRAINT uq_x UNIQUE USING INDEX uq_tmp;

  • ON CONFLICT 用部分唯一索引时，推断子句必须带上完全一致的谓词，否则报 no unique or exclusion constraint matching the ON CONFLICT specification。

    sql
    -- 正确
INSERT INTO user_addresses (user_id, address, is_default) VALUES (1, 'X', true)
ON CONFLICT (user_id) WHERE is_default DO NOTHING;

  • 预编译语句 + generic plan 会让部分索引失效。 参数化的列恰好是谓词列时，优先把谓词写成 SQL 文本里的字面量，而不是绑定参数。

  • 不要给「谓词列之外」的查询指望部分索引。 部分索引只覆盖满足谓词的行，其他查询需要另建索引。

  • 谓词过于复杂的部分索引收益递减。 谓词里堆十几个 OR / IN 条件，既难被蕴含匹配，也让维护成本上升；不如拆成几个语义清晰的小索引。

最佳实践 ​

  • 从「数据分布严重倾斜」的列上找部分索引的机会。 先用统计信息确认某个值只占极小比例，再决定谓词。

    sql
    SELECT status, count(*), round(100.0 * count(*) / sum(count(*)) OVER (), 2) AS pct
FROM jobs
GROUP BY status
ORDER BY count(*) DESC;

  • 把部分索引的谓词写成「最能表达业务规则」的最小条件。 例如软删除表统一用 WHERE deleted_at IS NULL，队列表统一用 WHERE status IN ('pending','running')，让谓词成为团队共识而非个人偏好。

    sql
    CREATE INDEX idx_users_alive_email ON users (email) WHERE deleted_at IS NULL;

  • 条件唯一性一律用部分唯一索引表达，不要靠应用层「先查再插」——那种做法在并发下必然漏判。

    sql
    CREATE UNIQUE INDEX uq_user_default_address ON user_addresses (user_id) WHERE is_default;

  • 部分索引 + INCLUDE 覆盖列，让「高频小查询」既不回表又不过度索引。

    sql
    CREATE INDEX idx_jobs_pending_inc
  ON jobs (created_at) INCLUDE (user_id) WHERE status = 'pending';

  • 给部分索引起能自解释的名字，把谓词意图写进名字（如 idx_jobs_active、uq_jobs_primary），因为 \d 里的索引名是运维第一眼看到的东西。

  • 每建一个部分索引，就在代码仓库里记录它约定的查询形态。 谓词蕴含是隐式契约，写进注释或文档才能避免后续有人改了查询却在纳闷「索引怎么没生效」。

  • 定期复核部分索引的使用率与体积，idx_scan = 0 且体积大的要提上清理日程。

    sql
    SELECT indexrelid::regclass AS index_name,
       idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'jobs'
ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

  • 建索引与改索引都走变更流程：先 CONCURRENTLY 建好、ANALYZE、用 EXPLAIN 验证命中，再观察一段时间使用率，最后才考虑清理旧索引。

下一节

继续学习：覆盖索引（INCLUDE）

参考链接 ​