PHP小码农

Appearance

CREATE INDEX 基础 ​

上一节讲清了 B-Tree 能做什么，但要真正用起来，绕不开 CREATE INDEX 这条命令。它看起来简单，参数却不少：唯一性、并发、覆盖列、部分索引、存储参数、表空间……每一个都对应一种生产环境的真实需求。

本节把 CREATE INDEX 的完整语法拆开逐个讲透，重点说明 CONCURRENTLY 在线建索引的原理与限制、CREATE INDEX 与 ALTER TABLE ADD CONSTRAINT 的区别、索引对写入的影响，以及如何用 EXPLAIN、系统表验证索引是否真的被用上。学完这一节，你能在生产库上安全地加索引、删索引，并判断一次索引变更是否成功。

前置知识

阅读本节前，建议先了解：B-Tree 索引原理

基础概念 ​

CREATE INDEX 做了什么 ​

一条 CREATE INDEX 在 PostgreSQL 内部大致经历三个阶段：

text
1. 解析与检查
   ├── 校验表存在、列存在、表达式是否 IMMUTABLE
   ├── 校验操作符类是否存在（如 text_pattern_ops）
   └── 检查同名关系是否已存在

2. 构建索引
   ├── 扫描全表，把每一行转成索引条目
   ├── 排序后批量写入索引页（不再是逐行插入，速度快得多）
   └── 在系统目录 pg_class / pg_index 中登记

3. 收尾
   ├── 标记索引为 valid
   ├── 更新 relcache / 失效旧计划缓存
   └── 通知统计收集器

关键一点：建索引走的是「排序 + 批量写」而非「逐行插入」。这也是为什么「批量导入后一次建索引」远比「先建索引再逐行导入」快。

索引命名规则 ​

索引名在同一 schema 内必须唯一，且不能与表、序列、视图、物化视图、外部表重名——因为它们共用 pg_class 一个命名空间。

情况行为
显式指定名字使用该名字，冲突则报错
省略名字自动生成，通常是 表名_列名_idx（冲突时加数字后缀）
名字含 schema不允许，索引总与父表同 schema
text
-- 自动生成的典型名字
CREATE INDEX ON users (email);          → users_email_idx
CREATE INDEX ON orders (customer_id);   → orders_customer_id_idx
CREATE UNIQUE INDEX ON t (a, b);        → t_a_b_key  （唯一/主键用 _key / _pkey）

命名规范建议

生产环境强烈建议显式命名。自动名字在加列、改列后含义会漂移，而显式命名如 idx_orders_customer_created 能自我说明用途，便于监控与运维。

CREATE INDEX vs ALTER TABLE ADD CONSTRAINT ​

两者都能产生索引，但目的不同：

维度CREATE UNIQUE INDEXALTER TABLE ... ADD CONSTRAINT
目的性能 / 唯一性都行声明业务约束
是否可延迟（DEFERRABLE）不能可以
是否可被外键引用不能可以
是否出现在 \d 的 Constraints 段否是
是否可 ON CONFLICT ON CONSTRAINT否是
是否可被 pg_dump 还原为约束否（还原为索引）是

一句话：要表达「这是业务规则」用约束，要表达「这是性能优化」用索引。 唯一索引只在「约束语义」上等价于 UNIQUE 约束，但缺少可延迟性与外键可引用性。

在线建索引（CONCURRENTLY）的原理 ​

普通 CREATE INDEX 会持有 SHARE 锁，阻塞所有写操作（读不受影响）。对大表来说，这个阻塞可能长达数小时。CONCURRENTLY 通过三阶段避免长时间阻塞：

text
阶段 1（事务 1）：在系统目录登记一个 INVALID 索引
        │
        ▼
阶段 2（事务 2）：第一次全表扫描，构建索引内容
        │  等待所有「能修改该表」的事务结束
        ▼
阶段 3（事务 3）：第二次扫描，补齐阶段 2 期间产生的新行
        │  等待所有「快照早于阶段 3」的事务结束
        ▼
标记 VALID，可用

代价与限制：

項说明
慢要扫两遍表，且要等事务；总耗时明显更长
占资源构建期间额外 CPU / I/O 会拖慢其他查询
不能包在事务块里BEGIN; CREATE INDEX CONCURRENTLY ...; COMMIT; 会报错
失败会留残骸失败后留下 INVALID 索引，会拖慢写入却不被查询使用
分区表不能在分区表上直接 CONCURRENTLY（可对每个分区单独执行，或 PG 支持的方式）

失败的 INVALID 索引长这样：

text
postgres=# \d tab
                     Table "public.tab"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 col    | integer |           |          |
Indexes:
    "idx" btree (col) INVALID

索引对写操作的影响 ​

每个索引都是写放大（write amplification）的来源：

操作需要维护的索引动作
INSERT每个索引插入 1 个条目
UPDATE（改索引列）每个索引删除旧条目 + 插入新条目
UPDATE（不改索引列）若新版本不在原页（HOT 更新失败），仍需更新索引指向
DELETE每个索引标记删除（真正回收靠 VACUUM）

所以「表上有 N 个索引，一次插入就是 N+1 次写入」。索引不是越多越好。

HOT 更新

当 UPDATE 只改非索引列、且新页与原页在同一页内时，PostgreSQL 使用 HOT（Heap-Only Tuple）更新，可以完全跳过索引维护。一旦改动了建了索引的列，HOT 就失效，所有索引都要更新。

索引类型与默认操作符类 ​

索引方法关键词适用
B-Treebtree（默认）等值、范围、排序、唯一
Hashhash仅等值
GiSTgist几何、范围、KNN
SP-GiSTspgist非平衡空间划分
GINgin数组、JSONB、全文
BRINbrin超大表、物理相关的列

验证索引是否被使用 ​

光「建了索引」不等于「用上了索引」。验证的三个层次：

层次手段回答的问题
单条语句EXPLAIN (ANALYZE, BUFFERS)这条 SQL 用没用索引
累计统计pg_stat_user_indexes.idx_scan这个索引长期有没有被用
物理健康pgstatindex() / pg_relation_size()索引体积、膨胀、层数是否合理

语法与用法 ​

text
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] 索引名 ]
    ON [ ONLY ] 表名 [ USING 方法 ]
    ( { 列名 | ( 表达式 ) }
        [ COLLATE 排序规则 ]
        [ 操作符类 [ ( 操作符类参数 = 值 [, ...] ) ] ]
        [ ASC | DESC ]
        [ NULLS { FIRST | LAST } ] [, ...] )
    [ INCLUDE ( 列名 [, ...] ) ]
    [ NULLS [ NOT ] DISTINCT ]
    [ WITH ( 存储参数 [= 值] [, ... ] ) ]
    [ TABLESPACE 表空间名 ]
    [ WHERE 谓词 ]

最小可运行示例：

sql
DROP TABLE IF EXISTS demo_index;

CREATE TABLE demo_index (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

CREATE INDEX idx_demo_index_email ON demo_index (email);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

完整语法逐参数拆解 ​

把语法槽位一个个填满，看看每个位置能写什么：

sql
DROP TABLE IF EXISTS idx_demo;

CREATE TABLE idx_demo (
  id         bigint GENERATED ALWAYS AS IDENTITY,
  email      text        NOT NULL,
  status     text        NOT NULL DEFAULT 'active',
  created_at timestamptz NOT NULL DEFAULT now()
);

-- 唯一 + 并发 + 不存在才建 + 指定方法 + 操作符类 + 排序 + NULL 位置
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS idx_idx_demo_email
  ON idx_demo USING btree (email text_pattern_ops DESC NULLS LAST);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

各槽位的取值约束：

槽位可选值备注
UNIQUE有 / 无有则强制唯一
CONCURRENTLY有 / 无不能用于临时表、不能用于事务块
IF NOT EXISTS有 / 无单独用无效，必须写索引名
ONLY有 / 无分区表不递归到子分区
USINGbtree/hash/gist/spgist/gin/brin默认 btree
COLLATE排序规则名影响比较与排序
opclass操作符类名如 text_pattern_ops
ASC/DESC排序方向默认 ASC
NULLS FIRST/LASTNULL 位置ASC 默认 LAST，DESC 默认 FIRST
INCLUDE列名列表非键列，B-Tree/GiST/SP-GiST 支持
NULLS NOT DISTINCT有 / 无唯一索引下 NULL 视为相等（PG 15+）
WITH存储参数fillfactor、deduplicate_items 等
TABLESPACE表空间名把索引放独立磁盘
WHERE布尔谓词生成部分索引

IF NOT EXISTS 与幂等部署 ​

在自动化脚本里，重复执行建索引会报错并中断流程。IF NOT EXISTS 解决这个问题：

sql
CREATE INDEX IF NOT EXISTS idx_idx_demo_email ON idx_demo (email);
text
NOTICE:  relation "idx_idx_demo_email" already exists, skipping
CREATE INDEX

注意输出是 NOTICE 而不是报错——脚本可以继续执行。

IF NOT EXISTS 不校验定义

它只检查名字。如果已存在一个同名但结构完全不同的索引（比如建在别的列上），它依然跳过，不会报错也不会修正。所以只在「确定名字唯一对应一种定义」时使用。

ALTER TABLE ADD CONSTRAINT 与 USING INDEX ​

给已存在的表加主键/唯一约束时，PostgreSQL 有两种路径，性能差异巨大：

路径一：直接加约束（会隐式建索引并全程持锁）

sql
ALTER TABLE idx_demo ADD CONSTRAINT idx_demo_email_key UNIQUE (email);
text
ALTER TABLE

路径二：先并发建唯一索引，再把索引「提升」为约束（生产推荐）

sql
-- 第 1 步：并发建唯一索引（此时已经保证唯一性）
CREATE UNIQUE INDEX CONCURRENTLY idx_demo_email_uq ON idx_demo (email);

-- 第 2 步：把索引挂成约束（瞬间完成，不重新扫描）
ALTER TABLE idx_demo
  ADD CONSTRAINT idx_demo_email_key UNIQUE USING INDEX idx_demo_email_uq;
text
CREATE INDEX
ALTER TABLE

对比：

方式锁耗时失败残留
ADD CONSTRAINT UNIQUE全程 SHARE 阻塞写一次扫描无（回滚）
CREATE UNIQUE INDEX CONCURRENTLY + USING INDEX建索引不阻塞两次扫描 + 瞬时挂接可能留 INVALID 索引

用 USING INDEX 还可以给主键「换索引」：

sql
-- 把已有唯一索引变成主键
ALTER TABLE idx_demo
  ADD CONSTRAINT idx_demo_pkey PRIMARY KEY USING INDEX idx_demo_email_uq;
text
ALTER TABLE

覆盖列 INCLUDE 与仅索引扫描 ​

INCLUDE 把列作为非键列挂在叶子条目上，不参与排序、不参与唯一性判断，但能被仅索引扫描直接读出：

sql
DROP TABLE IF EXISTS inc_demo;

CREATE TABLE inc_demo (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku    text NOT NULL,
  price  numeric(10,2) NOT NULL,
  stock  integer NOT NULL
);

INSERT INTO inc_demo (sku, price, stock)
SELECT 'SKU-' || lpad(g::text, 6, '0'), (random()*100)::numeric(10,2), (random()*1000)::int
FROM generate_series(1, 50000) AS g;

CREATE INDEX idx_inc_demo_sku ON inc_demo (sku) INCLUDE (price, stock);
VACUUM ANALYZE inc_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 50000
CREATE INDEX
VACUUM
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT sku, price, stock FROM inc_demo WHERE sku = 'SKU-000777';
text
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Only Scan using idx_inc_demo_sku on inc_demo (actual rows=1 loops=1)
   Index Cond: (sku = 'SKU-000777'::text)
   Heap Fetches: 0
   Buffers: shared hit=4
(4 rows)

Heap Fetches: 0 说明整条查询没回表。

INCLUDE 的三条硬限制

  1. 不能是表达式（因为无法用于仅索引扫描）；
  2. 索引条目超限会导致插入失败，别放宽列；
  3. 有 INCLUDE 列的 B-Tree 不使用去重，体积会更大。

部分索引 WHERE ​

部分索引只索引满足条件的行子集，体积小、维护便宜：

sql
DROP TABLE IF EXISTS task_demo;

CREATE TABLE task_demo (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status    text NOT NULL,
  payload   jsonb NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO task_demo (status, payload)
SELECT
  CASE WHEN g % 100 = 0 THEN 'pending' ELSE 'done' END,
  jsonb_build_object('n', g)
FROM generate_series(1, 100000) AS g;

-- 只索引 1% 的待处理任务
CREATE INDEX idx_task_demo_pending
  ON task_demo (created_at)
  WHERE status = 'pending';

VACUUM ANALYZE task_demo;
text
DROP TABLE
CREATE TABLE
INSERT 0 100000
CREATE INDEX
VACUUM
sql
SELECT
  pg_size_pretty(pg_relation_size('idx_task_demo_pending')) AS partial_size,
  pg_size_pretty(pg_relation_size('task_demo_pkey'))       AS pkey_size;
text
 partial_size | pkey_size
--------------+-----------
 16 kB        | 3544 kB

仅索引 1000 行，索引只有 16 kB，而全表主键索引 3.5 MB。

部分索引要生效，查询条件必须逻辑上蕴含索引的 WHERE：

sql
-- 会走部分索引：条件里明确写了 status = 'pending'
EXPLAIN (COSTS OFF)
SELECT id FROM task_demo WHERE status = 'pending' AND created_at > now() - interval '1 day';
text
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Index Scan using idx_task_demo_pending on task_demo
   Index Cond: (created_at > (now() - '1 day'::interval))
   Filter: (status = 'pending'::text)
(3 rows)

存储参数：fillfactor 与 deduplicate_items ​

sql
CREATE INDEX idx_inc_demo_sku_hot
  ON inc_demo (sku)
  WITH (fillfactor = 70, deduplicate_items = on);
text
CREATE INDEX
参数适用方法默认含义
fillfactorbtree/hash/gist/spgist90（B-Tree）页填充率，低则留白多、分裂少
deduplicate_itemsbtreeon合并重复键为 posting list

调整已有索引的参数：

sql
ALTER INDEX idx_inc_demo_sku_hot SET (fillfactor = 60);
text
ALTER INDEX

为什么 fillfactor = 100 往往有害

页填满后任何一次插入都可能触发分裂，甚至引发「分裂洪水」。只有完全静态（永不增删改）的表才适合 100。

指定表空间 ​

把索引放到独立磁盘（如 SSD）能显著改善随机 I/O：

sql
-- 先确保表空间存在
CREATE TABLESPACE fast_ssd LOCATION '/mnt/ssd/pg_tblspc';

CREATE INDEX idx_inc_demo_sku_ts ON inc_demo (sku) TABLESPACE fast_ssd;
text
CREATE TABLESPACE
CREATE INDEX

检查索引所在表空间：

sql
SELECT c.relname AS index_name, ts.spcname AS tablespace
FROM pg_class c
JOIN pg_tablespace ts ON ts.oid = c.reltablespace
WHERE c.relname LIKE 'idx_inc_demo%';
text
         index_name          | tablespace
-----------------------------+------------
 idx_inc_demo_sku_ts         | fast_ssd
(1 row)

CONCURRENTLY 的失败与恢复 ​

并发建索引一旦失败（死锁、唯一冲突、磁盘满、被取消），会留下 INVALID 索引。它不被查询使用，但仍会拖慢写入，必须清理：

sql
-- 找出所有无效索引
SELECT
  c.relname AS index_name,
  n.nspname AS schema_name,
  t.relname AS table_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
JOIN pg_class t ON t.oid = i.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE NOT i.indisvalid;
text
 index_name | schema_name | table_name
------------+-------------+------------
(0 rows)

两种恢复方式：

sql
-- 方式一：删掉重建（推荐，语义最清晰）
DROP INDEX CONCURRENTLY idx_bad_index;
CREATE INDEX CONCURRENTLY idx_bad_index ON big_table (col);

-- 方式二：直接重建（保留原名）
REINDEX INDEX CONCURRENTLY idx_bad_index;
text
DROP INDEX
CREATE INDEX
REINDEX

DROP INDEX 与 CONCURRENTLY ​

text
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] 索引名 [, ...] [ CASCADE | RESTRICT ]
选项说明
CONCURRENTLY不阻塞读写，但只能指定一个索引名
IF EXISTS不存在不报错
CASCADE连带删除依赖对象（如外键）
RESTRICT有依赖则拒绝（默认）

DROP INDEX CONCURRENTLY 的限制比 CREATE 更多：

sql
-- 合法
DROP INDEX CONCURRENTLY idx_inc_demo_sku_hot;
text
DROP INDEX

以下都不能用、或会报错：

操作是否允许
DROP INDEX CONCURRENTLY a, b;不允许，只能一个
DROP INDEX CONCURRENTLY ... CASCADE;不允许
在事务块内 DROP INDEX CONCURRENTLY不允许
用于分区表索引不允许
删除支持 UNIQUE / PRIMARY KEY 约束的索引不允许（要用 ALTER TABLE DROP CONSTRAINT）
sql
-- 删除约束索引的正确方式
ALTER TABLE idx_demo DROP CONSTRAINT idx_demo_email_key;
text
ALTER TABLE

验证索引是否被使用 ​

第一步：强制 ANALYZE，让统计信息反映真实数据分布：

sql
ANALYZE inc_demo;
text
ANALYZE

第二步：EXPLAIN 看计划里有没有 Index Cond：

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM inc_demo WHERE sku = 'SKU-000777';
text
                                  QUERY PLAN
---------------------------------------------------------------------------------
 Index Only Scan using idx_inc_demo_sku on inc_demo (actual rows=1 loops=1)
   Index Cond: (sku = 'SKU-000777'::text)
   Heap Fetches: 0
(3 rows)

第三步：必要时临时关掉顺序扫描以确认索引可用（仅用于排查，别在生产长期设置）：

sql
SET enable_seqscan = off;
EXPLAIN (COSTS OFF)
SELECT id FROM inc_demo WHERE sku = 'SKU-000777';
RESET enable_seqscan;
text
SET
                                  QUERY PLAN
---------------------------------------------------------------------------------
 Index Only Scan using idx_inc_demo_sku on inc_demo
   Index Cond: (sku = 'SKU-000777'::text)
(2 rows)
RESET

第四步：长期视角看 idx_scan：

sql
SELECT
  indexrelid::regclass AS index_name,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'inc_demo';
text
      index_name      | idx_scan | idx_tup_read | idx_tup_fetch
----------------------+----------+--------------+---------------
 inc_demo_pkey        |        0 |            0 |             0
 idx_inc_demo_sku     |        3 |            3 |             0
(2 rows)

idx_tup_fetch = 0 印证了仅索引扫描（不需要从堆表取行）。

用系统表读取索引定义 ​

sql
-- 单索引定义
SELECT indexrelid::regclass AS index_name, indexdef
FROM pg_indexes
WHERE indexname = 'idx_inc_demo_sku';
text
      index_name       |                                indexdef
-----------------------+------------------------------------------------------------------------
 idx_inc_demo_sku      | CREATE INDEX idx_inc_demo_sku ON public.inc_demo USING btree (sku)
(1 row)
sql
-- 更精确的还原（含存储参数）
SELECT pg_get_indexdef('idx_inc_demo_sku_hot'::regclass);
text
                                                          pg_get_indexdef
----------------------------------------------------------------------------------------------------------------------------------
 CREATE INDEX idx_inc_demo_sku_hot ON public.inc_demo USING btree (sku) WITH (fillfactor='70', deduplicate_items='true')
(1 row)

索引的锁行为对照 ​

不同 CREATE INDEX 变体持有的锁不同，直接决定生产可用性：

语句对写操作的阻塞对读操作
CREATE INDEX阻塞（SHARE 锁）不阻塞
CREATE INDEX CONCURRENTLY不阻塞不阻塞
REINDEX INDEX阻塞阻塞（ACCESS EXCLUSIVE）
REINDEX INDEX CONCURRENTLY不阻塞不阻塞
DROP INDEX阻塞（ACCESS EXCLUSIVE）阻塞
DROP INDEX CONCURRENTLY不阻塞不阻塞

实战示例 ​

场景：一张 200 万行的订单表，需要在业务不停机的前提下，从「无索引」演进到「主键 + 复合索引 + 部分索引」，并全程验证。

第 1 步：建表（先不建主键）并灌入 200 万行

sql
DROP TABLE IF EXISTS prod_orders CASCADE;

CREATE TABLE prod_orders (
  id          bigint,
  customer_id integer     NOT NULL,
  status      text        NOT NULL,
  amount      numeric(12,2) NOT NULL,
  created_at  timestamptz NOT NULL
);

INSERT INTO prod_orders (id, customer_id, status, amount, created_at)
SELECT
  g,
  (random() * 200000)::int,
  CASE WHEN g % 50 = 0 THEN 'pending' ELSE 'done' END,
  (random() * 9000)::numeric(12,2),
  now() - (g || ' seconds')::interval
FROM generate_series(1, 2000000) AS g;

ANALYZE prod_orders;
text
DROP TABLE
CREATE TABLE
INSERT 0 2000000
ANALYZE

第 2 步：并发建立唯一索引（为后面挂主键做准备）

sql
CREATE UNIQUE INDEX CONCURRENTLY uq_prod_orders_id ON prod_orders (id);
text
CREATE INDEX

第 3 步：把唯一索引提升为主键（瞬时完成）

sql
ALTER TABLE prod_orders ADD CONSTRAINT prod_orders_pkey PRIMARY KEY USING INDEX uq_prod_orders_id;
text
ALTER TABLE

验证主键已生效：

sql
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'prod_orders'::regclass;
text
     conname      | contype |      definition
------------------+---------+----------------------
 prod_orders_pkey | p       | PRIMARY KEY (id)
(1 row)

第 4 步：为高频查询建立复合索引

业务查询是「查某客户最近的订单，按时间倒序取前 20 条」。

sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount, created_at
FROM prod_orders
WHERE customer_id = 88888
ORDER BY created_at DESC
LIMIT 20;
text
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   Buffers: shared hit=6483
   ->  Sort (actual rows=10 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         Buffers: shared hit=6483
         ->  Seq Scan on prod_orders (actual rows=10 loops=1)
               Filter: (customer_id = 88888)
               Rows Removed by Filter: 1999990
(8 rows)

顺序扫描 200 万行。建立复合索引：

sql
CREATE INDEX CONCURRENTLY idx_prod_orders_cust_created
  ON prod_orders (customer_id, created_at DESC);
ANALYZE prod_orders;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount, created_at
FROM prod_orders
WHERE customer_id = 88888
ORDER BY created_at DESC
LIMIT 20;
text
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   Buffers: shared hit=8
   ->  Index Scan Backward using idx_prod_orders_cust_created on prod_orders (actual rows=10 loops=1)
         Index Cond: (customer_id = 88888)
         Buffers: shared hit=7
(5 rows)

两个变化：Sort 节点消失（索引本身有序），缓冲区从 6483 降到 8。

第 5 步：为「待处理任务」建立部分索引

sql
CREATE INDEX CONCURRENTLY idx_prod_orders_pending
  ON prod_orders (created_at)
  WHERE status = 'pending';
ANALYZE prod_orders;
text
CREATE INDEX
ANALYZE
sql
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM prod_orders WHERE status = 'pending';
text
                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   Buffers: shared hit=5
   ->  Index Only Scan using idx_prod_orders_pending on prod_orders (actual rows=40000 loops=1)
         Heap Fetches: 0
         Buffers: shared hit=4
(5 rows)

第 6 步：对比三个索引的体积

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 = 'prod_orders'
ORDER BY pg_relation_size(indexrelid) DESC;
text
            index_name             | idx_scan |  size
-----------------------------------+----------+---------
 prod_orders_pkey                  |        0 | 47 MB
 idx_prod_orders_cust_created      |        1 | 101 MB
 idx_prod_orders_pending           |        1 | 1304 kB
(3 rows)

部分索引只占 1.3 MB，因为只索引了 2% 的行。

第 7 步：检查有没有残留的无效索引

sql
SELECT c.relname AS index_name
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'prod_orders'::regclass AND NOT i.indisvalid;
text
 index_name
------------
(0 rows)

第 8 步：清理

sql
DROP TABLE prod_orders;
DROP TABLE inc_demo;
DROP TABLE task_demo;
DROP TABLE idx_demo;
DROP TABLE demo_index;

注意事项 ​

  • CREATE INDEX CONCURRENTLY 不能放在事务块中，否则直接报错。

    sql
    -- 错误！
BEGIN;
CREATE INDEX CONCURRENTLY idx_x ON t (col);
COMMIT;

  • 并发建索引失败会留下 INVALID 索引，它不服务查询却拖慢写入，必须清理。

    sql
    SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

  • IF NOT EXISTS 只比名字不比定义，同名不同定义会被静默跳过。

    sql
    -- 危险：如果已存在同名索引，这句什么也不做
CREATE INDEX IF NOT EXISTS idx_x ON t (other_col);

  • DROP INDEX CONCURRENTLY 一次只能删一个索引，且不支持 CASCADE。

    sql
    -- 错误！只能指定一个索引
DROP INDEX CONCURRENTLY a, b;

  • 不能直接用 DROP INDEX 删除约束背后的索引，要用 ALTER TABLE DROP CONSTRAINT。

    sql
    -- 错误！会报 "cannot drop index ... because constraint ... requires it"
DROP INDEX prod_orders_pkey;

  • 表达式索引里的函数必须是 IMMUTABLE，now()、random() 这类会报错。

    sql
    -- 错误！now() 不是 IMMUTABLE
CREATE INDEX idx_bad ON t ((now()::date));

  • 索引不是越多越好。每个索引都会拖慢所有写操作，还会占用缓存。

    sql
    -- 不推荐：一张表上挂十几个索引

  • 并发建索引期间不要同时跑其他并发建索引，它们的快照互相等待，会显著延长耗时。

  • 建索引别忘 ANALYZE，否则优化器可能因为统计过期而不使用新索引。

    sql
    CREATE INDEX CONCURRENTLY idx_x ON t (col);
ANALYZE t;   -- 关键的一步

  • fillfactor = 100 在高更新表上是有害的，会导致大量页分裂。

    sql
    -- 不推荐（除非表完全静态）
CREATE INDEX idx_bad ON hot_table (col) WITH (fillfactor = 100);

  • INCLUDE 列不能是表达式，也不能用于唯一性判断。

    sql
    -- 错误！表达式不能作为 INCLUDE 列
CREATE INDEX idx_bad ON t (a) INCLUDE ((b + 1));

  • 临时表的 CREATE INDEX 始终是非并发的，CONCURRENTLY 对临时表无意义（也更快）。

  • 索引名冲突发生在整个 schema 级，索引名不能与同 schema 的表名重复。

最佳实践 ​

  • 大表建索引一律用 CONCURRENTLY，并在失败后检查残留：

    sql
    CREATE INDEX CONCURRENTLY idx_big ON big_table (col);
SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'big_table'::regclass AND NOT i.indisvalid;

  • 给已有表加主键/唯一约束时，先并发建索引再 USING INDEX 挂接：

    sql
    CREATE UNIQUE INDEX CONCURRENTLY uq_t_id ON t (id);
ALTER TABLE t ADD CONSTRAINT t_pkey PRIMARY KEY USING INDEX uq_t_id;

  • 明确命名所有索引，用 idx_表_列 的约定，便于监控和排查：

    sql
    CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

  • 建完索引立即 ANALYZE：

    sql
    CREATE INDEX CONCURRENTLY idx_x ON t (col);
ANALYZE t;

  • 小结果集的过滤条件用部分索引，能大幅降低索引体积与维护成本：

    sql
    CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';

  • 批量导入采用「先删索引 → 导入 → 重建索引」策略，比逐行维护快得多：

    sql
    DROP INDEX idx_orders_customer_created;
-- COPY / INSERT 大批量数据
CREATE INDEX idx_orders_customer_created ON orders (customer_id, created_at DESC);

  • 需要覆盖查询时用 INCLUDE，但只放窄列：

    sql
    CREATE INDEX idx_orders_cover ON orders (customer_id) INCLUDE (amount);

  • 高频更新的索引调低 fillfactor：

    sql
    CREATE INDEX idx_hot ON hot_table (col) WITH (fillfactor = 70);

  • 定期清理无用索引，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 schemaname = 'public' AND idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

  • 用 EXPLAIN (ANALYZE, BUFFERS) 而非仅 EXPLAIN 验证效果，前者才有真实行数与 I/O：

    sql
    EXPLAIN (ANALYZE, COSTS OFF, BUFFERS) SELECT ... ;

下一节

继续学习：多列索引

参考链接 ​