多列索引

单列索引解决「按一个条件过滤」的问题，但真实查询几乎总带多个条件：WHERE customer_id = ? AND created_at > ?。这时你有两个选择——建两个单列索引让优化器组合，或建一个多列索引。

多列索引（multicolumn index）是 B-Tree 最重要的进阶能力之一，它能不能被用上，完全取决于列的顺序是否匹配查询的最左前缀。本节讲清最左前缀原则、列顺序的选择方法、PostgreSQL 18 新增的跳读扫描（Index Skip Scan）、多列索引与多个单列索引的取舍，以及如何让一个索引同时服务 WHERE 和 ORDER BY。

前置知识

阅读本节前，建议先了解：CREATE INDEX 基础

基础概念

多列索引的物理结构

多列 B-Tree 把多个列的值按顺序拼接成一个复合键，然后整体排序：

CREATE INDEX idx_demo_ab ON demo (a, b);

索引里的键（拼接后排序）：
  (1, 10)
  (1, 20)
  (1, 30)
  (2, 10)
  (2, 50)
  (3, 10)

排序规则：先按 a 排序；a 相同时再按 b 排序。

这就解释了为什么它叫「前缀索引」——索引只能帮你定位从最左列开始的连续前缀。

查询条件	能否用 (a, b) 索引	原因
a = 1	能（部分使用）	最左列等值
a = 1 AND b = 20	能（完全使用）	两列都用上
a = 1 AND b > 15	能（完全使用）	等值 + 范围
b = 20	旧版本不能，PG 18+ 可跳读扫描	缺最左列
b = 20 AND a = 1	能	优化器不看书写顺序

最左前缀原则

最左前缀原则（leftmost prefix rule）：只有当查询条件包含索引的第一个列时，索引才能被用于定位。

索引 (a, b, c)

能用：a            ← 前缀 (a)
能用：a, b         ← 前缀 (a, b)
能用：a, b, c      ← 全键
能用：a, c         ← 只用 a（c 无法用于定位，但可 Recheck）
不能用：b
不能用：b, c
不能用：c

书写顺序无关，列集合相关

WHERE b = 2 AND a = 1 与 WHERE a = 1 AND b = 2 对优化器完全等价——它会把条件重排后去匹配索引。列顺序指的是索引定义里的列顺序，不是 SQL 里的书写顺序。

列的「角色」：等值列 vs 范围列

在多列索引里，每一列的用法决定它是「等值定位」还是「范围定位」：

索引 (a, b, c)
查询 WHERE a = 1 AND b = 2 AND c > 5

 a: 等值 → 定位到一个精确点
 b: 等值 → 在该点下再定位
 c: 范围 → 在 (1, 2) 这个前缀内扫一段
 → 三列全部用于 Index Cond

索引 (a, b, c)
查询 WHERE a = 1 AND b > 2 AND c = 5

 a: 等值 → 定位
 b: 范围 → 扫一段
 c: 放弃 → 范围之后的列无法再用于定位；c = 5 只能作为 Filter
 → 只有 a、b 进入 Index Cond

结论：范围列之后的所有列都失去索引定位能力。 这是列顺序选择的核心依据。

列顺序的黄金法则

规则	说明
等值列在前，范围列在后	范围一旦出现，其后的列无法定位
选择性高的列优先	但这条要让位于「等值 / 范围」规则
经常单独查询的列放最左	这样它自己也能受益
排序列尽量靠右（但在范围列之后）	WHERE a = ? ORDER BY b → (a, b)
列数别超过 3 个	超过后收益递减，维护成本上升

BitmapAnd：多个单列索引的组合

如果不建多列索引，优化器可以用位图与（BitmapAnd）组合多个单列索引：

WHERE a = 1 AND b = 2

索引 idx_a：找出 a=1 的所有行 → 位图 A
索引 idx_b：找出 b=2 的所有行 → 位图 B
BitmapAnd(A, B)  → 交集 → 回表

代价对比：

方案	优点	缺点
一个多列索引 (a, b)	一次定位、I/O 最少、可用于排序	只服务特定前缀组合
两个单列索引	各自独立可用，组合灵活	要读两个索引 + 位图合并，且丢失顺序
两者都建	灵活性最高	写放大、占用空间

BitmapAnd 的隐含代价

位图扫描是「读索引 → 构建位图 → 按物理页序回表」，物理序不等于索引序，因此如果查询里还带 ORDER BY，一定多一个 Sort 节点。多列索引则能顺手消掉这个 Sort。

PostgreSQL 18 的跳读扫描

PostgreSQL 18 引入了 跳读扫描（Index Skip Scan）：即使查询不带最左列，也可能用上多列索引。

索引 (a, b)
查询 WHERE b = 20

旧版本：必须扫全表
PG 18+：在每个 a 的取值下，跳着找 b = 20
        a=1 → 找 b=20
        a=2 → 找 b=20
        a=3 → 找 b=20
        ...

它的前提是最左列的基数（distinct 值个数）足够小，否则「跳」的次数太多，优化器会判断不如顺序扫描。

条件	是否启用跳读
最左列只有少数几个不同值	倾向于启用
最左列有几十万个不同值	通常不用，代价太高
索引的第一列被表达式或函数跳过	一般不行

多列索引与排序

多列索引的叶子页按复合键排序，因此最后一个「等值列」之后的列可以天然提供排序：

索引 (customer_id, created_at)

WHERE customer_id = 777 ORDER BY created_at
 → customer_id 等值定位，created_at 在索引内已有序 → 无 Sort

但要注意：如果排序列之前还有范围条件，顺序就可能被打破：

索引 (a, b, c)
WHERE a = 1 AND b > 2 ORDER BY c
 → b 是范围，c 的顺序在 b 变化时会重置 → 需要 Sort

多列索引 vs 覆盖索引

概念	列的作用	是否参与定位	是否参与排序
键列（key column）	参与排序与定位	是	是
覆盖列（INCLUDE）	只用于读取	否	否

所以「用多列索引覆盖查询」和「用 INCLUDE 覆盖查询」是两回事：前者增加键宽度、影响排序语义，后者只增加叶子载荷。

语法与用法

-- 多列索引：列顺序即键顺序
CREATE INDEX 索引名 ON 表名 (列1, 列2 [, 列3 ...]);

-- 每列都可以指定方向与 NULL 位置
CREATE INDEX 索引名 ON 表名 (列1 ASC NULLS LAST, 列2 DESC NULLS FIRST);

-- 混合键列 + 覆盖列
CREATE INDEX 索引名 ON 表名 (列1, 列2) INCLUDE (列3, 列4);

-- 表达式可以参与多列索引
CREATE INDEX 索引名 ON 表名 (列1, (lower(列2)));

-- 多列唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列1, 列2);

-- 部分多列索引
CREATE INDEX 索引名 ON 表名 (列1, 列2) WHERE 条件;

-- 在线建立
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (列1, 列2);

最小可运行示例：

DROP TABLE IF EXISTS demo_multi;

CREATE TABLE demo_multi (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  a     integer NOT NULL,
  b     integer NOT NULL
);

CREATE INDEX idx_demo_multi_ab ON demo_multi (a, b);

DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明

最左前缀原则的实测

先建数据与索引，再逐条验证哪些条件能用索引：

DROP TABLE IF EXISTS mc_demo;

CREATE TABLE mc_demo (
  id bigint GENERATED ALWAYS AS IDENTITY,
  a  integer NOT NULL,
  b  integer NOT NULL,
  c  integer NOT NULL,
  payload text NOT NULL
);

INSERT INTO mc_demo (a, b, c, payload)
SELECT
  (random() * 100)::int,
  (random() * 1000)::int,
  (random() * 10000)::int,
  repeat('x', 50)
FROM generate_series(1, 300000) AS g;

CREATE INDEX idx_mc_demo_abc ON mc_demo (a, b, c);
ANALYZE mc_demo;

DROP TABLE
CREATE TABLE
INSERT 0 300000
CREATE INDEX
ANALYZE

用最左列查询：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42;

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Bitmap Heap Scan on mc_demo (actual rows=3000 loops=1)
   Recheck Cond: (a = 42)
   Heap Blocks: exact=2536
   ->  Bitmap Index Scan on idx_mc_demo_abc (actual rows=3000 loops=1)
         Index Cond: (a = 42)
(6 rows)

跳过最左列、只用第二列：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE b = 500;

                                QUERY PLAN
---------------------------------------------------------------------------
 Gather (actual rows=300 loops=1)
   Workers Planned: 2
   Buffers: shared hit=5264
   ->  Parallel Seq Scan on mc_demo (actual rows=100 loops=1)
         Filter: (b = 500)
         Rows Removed by Filter: 99900
(6 rows)

顺序扫描。这就是最左前缀原则的直接体现——列再多，不带第一列就等于没有。

范围列之后的列会失效

这是最容易被忽视、也最容易搞错列顺序的地方：

-- 索引 (a, b, c)，b 是范围条件
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42 AND b > 500 AND c = 7000;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Bitmap Heap Scan on mc_demo (actual rows=0 loops=1)
   Recheck Cond: ((a = 42) AND (b > 500))
   Filter: (c = 7000)
   Rows Removed by Filter: 1500
   Heap Blocks: exact=1173
   ->  Bitmap Index Scan on idx_mc_demo_abc (actual rows=1500 loops=1)
         Index Cond: ((a = 42) AND (b > 500))
(7 rows)

注意两点：Index Cond 里只有 a 和 b，c = 7000 降级成了 Filter。索引只用于定位 (a=42, b>500) 这一段，c 要靠回表后逐行过滤。

如果把 c 提到 b 前面（(a, c, b)），情况就不同了：

CREATE INDEX idx_mc_demo_acb ON mc_demo (a, c, b);
ANALYZE mc_demo;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42 AND b > 500 AND c = 7000;

                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Index Scan using idx_mc_demo_acb on mc_demo (actual rows=0 loops=1)
   Index Cond: ((a = 42) AND (c = 7000) AND (b > 500))
(3 rows)

三个列全部进入 Index Cond，一次定位到空集，零回表。这就是「等值列在前、范围列在后」的威力。

如何为多列查询选择列顺序

一个可执行的决策流程：

列出查询里出现的所有列
        │
        ├── 找出所有「等值条件」列 → 放最前面（选择性高的优先）
        │
        ├── 找出「范围条件」列 → 紧随其后
        │
        ├── 找出「ORDER BY」列 → 放在范围列之后（若无范围条件则紧跟等值列）
        │
        └── 其余只读列 → 放 INCLUDE

举例：

查询	最佳索引
WHERE a=? AND b=? AND c>?	(a, b, c)
WHERE a=? AND c>? AND b=?	(a, b, c)
WHERE a=? AND b>? AND c=?	(a, c, b)
WHERE a=? AND b=? ORDER BY c	(a, b, c)
WHERE a=? AND b>? ORDER BY c	若 c 选择性高：(a, c, b)；否则只能 (a, b) + Sort
WHERE a=? ORDER BY b DESC	(a, b DESC)

多列索引 vs 多个单列索引：BitmapAnd 实测

给 a、b 各建一个单列索引，观察优化器如何组合：

CREATE INDEX idx_mc_demo_a ON mc_demo (a);
CREATE INDEX idx_mc_demo_b ON mc_demo (b);
ANALYZE mc_demo;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42 AND b = 500;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Bitmap Heap Scan on mc_demo (actual rows=4 loops=1)
   Recheck Cond: ((a = 42) AND (b = 500))
   Heap Blocks: exact=4
   Buffers: shared hit=18
   ->  BitmapAnd (actual rows=4 loops=1)
         ->  Bitmap Index Scan on idx_mc_demo_a (actual rows=3000 loops=1)
               Index Cond: (a = 42)
         ->  Bitmap Index Scan on idx_mc_demo_b (actual rows=300 loops=1)
               Index Cond: (b = 500)
(9 rows)

BitmapAnd 节点明确出现。与多列索引对比：

维度	多列索引 (a, b)	两个单列索引 + BitmapAnd
索引扫描次数	1	2
是否保序	是	否
可服务的查询组合	以 a 为前缀的组合	a、b 任意独立组合
写放大	1 个索引	2 个索引

再看 OR 场景——BitmapOr：

EXPLAIN (COSTS OFF)
SELECT id FROM mc_demo WHERE a = 42 OR b = 500;

                             QUERY PLAN
------------------------------------------------------------------------
 Bitmap Heap Scan on mc_demo
   Recheck Cond: ((a = 42) OR (b = 500))
   ->  BitmapOr
         ->  Bitmap Index Scan on idx_mc_demo_a
               Index Cond: (a = 42)
         ->  Bitmap Index Scan on idx_mc_demo_b
               Index Cond: (b = 500)
(7 rows)

OR 条件无法用单个多列索引优化——多列索引只能表达「与」，表达不了「或」。这类查询只能靠多个单列索引 + BitmapOr。

BitmapAnd 与排序的冲突

位图扫描丢失顺序，这是选型时容易忽略的一点：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42 AND b = 500 ORDER BY c LIMIT 10;

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit (actual rows=4 loops=1)
   ->  Sort (actual rows=4 loops=1)
         Sort Key: c
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on mc_demo (actual rows=4 loops=1)
               Recheck Cond: ((a = 42) AND (b = 500))
               ->  BitmapAnd
                     ->  Bitmap Index Scan on idx_mc_demo_a
                     ->  Bitmap Index Scan on idx_mc_demo_b
(10 rows)

有 Sort 节点。改成 (a, b, c) 多列索引后：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE a = 42 AND b = 500 ORDER BY c LIMIT 10;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Limit (actual rows=4 loops=1)
   ->  Index Scan using idx_mc_demo_abc on mc_demo (actual rows=4 loops=1)
         Index Cond: ((a = 42) AND (b = 500))
(3 rows)

Sort 消失——a、b 等值定位后，c 在索引里天然有序。

多列索引与混合排序方向

当 ORDER BY 包含不同方向时，索引定义必须匹配：

CREATE INDEX idx_mc_demo_a_b_desc ON mc_demo (a, b DESC);
ANALYZE mc_demo;

EXPLAIN (COSTS OFF)
SELECT id FROM mc_demo WHERE a = 42 ORDER BY b DESC LIMIT 5;

                                   QUERY PLAN
----------------------------------------------------------------------------------
 Limit
   ->  Index Scan using idx_mc_demo_a_b_desc on mc_demo
         Index Cond: (a = 42)
(3 rows)

若索引写成 (a, b)，则 ORDER BY b DESC 需要反向扫描——单个方向反转仍然可行，但多列混合方向就未必：

索引 (a, b)：        正扫满足 ORDER BY a, b        （即 a ASC, b ASC）
                     反扫满足 ORDER BY a DESC, b DESC
索引 (a ASC, b DESC)：正扫满足 ORDER BY a ASC, b DESC
索引 (a DESC, b ASC)：正扫满足 ORDER BY a DESC, b ASC

PostgreSQL 18 的跳读扫描

在 PG 18 之前，WHERE b = ? 完全用不上 (a, b) 索引。PG 18 引入跳读扫描后：

-- 最左列 a 的基数很低（假设只有 5 个值）时，跳读扫描可能生效
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM mc_demo WHERE b = 500;

                                      QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Only Scan using idx_mc_demo_a_b_desc on mc_demo (actual rows=300 loops=1)
   Index Cond: (b = 500)
   Heap Fetches: 0
(3 rows)

跳读扫描把「一次范围扫」变成「对最左列的每个不同值各做一次定位」，因此：

最左列基数	跳读收益
极少（< 几十）	高，接近一次索引扫描
中等（几百到几千）	一般，可能不如顺序扫描
极高（几十万）	负收益，优化器不会选

跳读扫描不是万能的

它不能替代正确的列顺序。跳读本质上是在「最左列的每个值」上重复定位，开销随最左列基数线性增长。真正稳定的做法仍然是——按查询模式把正确的列放到最左边。

多列唯一索引

多列索引可以声明唯一，语义是「所有列的组合唯一」：

DROP TABLE IF EXISTS uc_demo;

CREATE TABLE uc_demo (
  id       bigint GENERATED ALWAYS AS IDENTITY,
  tenant   text NOT NULL,
  username text NOT NULL
);

CREATE UNIQUE INDEX uq_uc_demo_tenant_user ON uc_demo (tenant, username);
INSERT INTO uc_demo (tenant, username) VALUES ('t1', 'alice');

DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX
INSERT 0 1

同租户重名被拒绝：

INSERT INTO uc_demo (tenant, username) VALUES ('t1', 'alice');

ERROR:  duplicate key value violates unique constraint "uq_uc_demo_tenant_user"
DETAIL:  Key (tenant, username)=(t1, alice) already exists.

不同租户同名则允许：

INSERT INTO uc_demo (tenant, username) VALUES ('t2', 'alice');

INSERT 0 1

用系统表检查索引列顺序

SELECT
  i.indexrelid::regclass AS index_name,
  i.indnatts             AS total_attrs,
  i.indnkeyatts          AS key_attrs,
  i.indkey               AS key_columns,
  i.indisunique          AS is_unique
FROM pg_index i
WHERE i.indrelid = 'mc_demo'::regclass
ORDER BY i.indexrelid::regclass::text;

      index_name       | total_attrs | key_attrs | key_columns | is_unique
-----------------------+-------------+-----------+-------------+-----------
 idx_mc_demo_a         |           1 |         1 | 0           | f
 idx_mc_demo_a_b_desc  |           2 |         2 | 0 1         | f
 idx_mc_demo_abc       |           3 |         3 | 0 1 2       | f
 idx_mc_demo_acb       |           3 |         3 | 0 2 1       | f
 idx_mc_demo_b         |           1 |         1 | 1           | f
(5 rows)

indkey 里的数字是列的属性号（attnum - 1）。0 2 1 表示这个索引的键顺序是「第 1 列、第 3 列、第 2 列」。total_attrs 与 key_attrs 的差值就是 INCLUDE 列数。

实战示例

场景：一张多租户 SaaS 的审计日志表，三种典型查询模式，需要设计最少的索引覆盖它们。

编号	查询模式	频率
Q1	WHERE tenant_id = ? AND created_at >= ?	高
Q2	WHERE tenant_id = ? AND actor_id = ? ORDER BY created_at DESC	高
Q3	WHERE action = ?	低

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

DROP TABLE IF EXISTS audit_logs CASCADE;

CREATE TABLE audit_logs (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  integer     NOT NULL,
  actor_id   integer     NOT NULL,
  action     text        NOT NULL,
  detail     jsonb       NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO audit_logs (tenant_id, actor_id, action, detail, created_at)
SELECT
  (random() * 500)::int,
  (random() * 5000)::int,
  (ARRAY['login','logout','update','delete','export'])[1 + (g % 5)],
  jsonb_build_object('n', g),
  now() - (g || ' seconds')::interval
FROM generate_series(1, 1000000) AS g;

ANALYZE audit_logs;

DROP TABLE
CREATE TABLE
INSERT 0 1000000
ANALYZE

第 2 步：基线——Q1 无索引

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, action FROM audit_logs
WHERE tenant_id = 42 AND created_at >= now() - interval '30 minutes';

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on audit_logs (actual rows=240 loops=1)
   Filter: ((tenant_id = 42) AND (created_at >= (now() - '00:30:00'::interval)))
   Rows Removed by Filter: 999760
   Buffers: shared hit=13463
(4 rows)

第 3 步：为 Q1 建索引 (tenant_id, created_at)

CREATE INDEX CONCURRENTLY idx_audit_tenant_created
  ON audit_logs (tenant_id, created_at);
ANALYZE audit_logs;

CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, action FROM audit_logs
WHERE tenant_id = 42 AND created_at >= now() - interval '30 minutes';

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Index Scan using idx_audit_tenant_created on audit_logs (actual rows=240 loops=1)
   Index Cond: ((tenant_id = 42) AND (created_at >= (now() - '00:30:00'::interval)))
   Buffers: shared hit=22
(3 rows)

第 4 步：为 Q2 建索引 (tenant_id, actor_id, created_at DESC)

注意列顺序：两个等值列在前（tenant_id、actor_id），排序列在后（created_at DESC）。

CREATE INDEX CONCURRENTLY idx_audit_tenant_actor_created
  ON audit_logs (tenant_id, actor_id, created_at DESC);
ANALYZE audit_logs;

CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, action, created_at FROM audit_logs
WHERE tenant_id = 42 AND actor_id = 777
ORDER BY created_at DESC
LIMIT 10;

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Limit (actual rows=1 loops=1)
   Buffers: shared hit=6
   ->  Index Scan using idx_audit_tenant_actor_created on audit_logs (actual rows=1 loops=1)
         Index Cond: ((tenant_id = 42) AND (actor_id = 777))
         Buffers: shared hit=5
(5 rows)

无 Sort、缓冲区仅 6 次——索引同时承担了过滤与排序。

第 5 步：验证「范围列之后的列会失效」

故意用错误的列顺序建索引做对照：

CREATE INDEX idx_audit_wrong ON audit_logs (tenant_id, created_at, actor_id);
ANALYZE audit_logs;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM audit_logs
WHERE tenant_id = 42 AND actor_id = 777 AND created_at >= now() - interval '1 hour';

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Bitmap Heap Scan on audit_logs (actual rows=0 loops=1)
   Recheck Cond: ((tenant_id = 42) AND (created_at >= (now() - '01:00:00'::interval)))
   Filter: (actor_id = 777)
   Rows Removed by Filter: 450
   Heap Blocks: exact=286
   ->  Bitmap Index Scan on idx_audit_wrong (actual rows=450 loops=1)
         Index Cond: ((tenant_id = 42) AND (created_at >= (now() - '01:00:00'::interval)))
(7 rows)

actor_id 掉进 Filter。删掉这个错误索引：

DROP INDEX CONCURRENTLY idx_audit_wrong;

DROP INDEX

第 6 步：Q3（低选择性）的取舍

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT count(*) FROM audit_logs WHERE action = 'export';

                                 QUERY PLAN
-----------------------------------------------------------------------------
 Aggregate (actual rows=1 loops=1)
   ->  Seq Scan on audit_logs (actual rows=200000 loops=1)
         Filter: (action = 'export'::text)
         Rows Removed by Filter: 800000
(4 rows)

命中 20% 的行，顺序扫描就是最优解——不要给 action 建索引。

第 7 步：索引体积与使用统计

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

             index_name              | idx_scan |  size
-------------------------------------+----------+---------
 audit_logs_pkey                     |        0 | 31 MB
 idx_audit_tenant_actor_created      |        1 | 62 MB
 idx_audit_tenant_created            |        1 | 42 MB
(3 rows)

第 8 步：清理

DROP TABLE audit_logs;
DROP TABLE mc_demo;
DROP TABLE uc_demo;
DROP TABLE demo_multi;

注意事项

• 不带最左列的查询用不了多列索引（除非 PG 18 跳读扫描生效且最左列基数很低）。

  -- 索引是 (a, b)，下面这句只能顺序扫描
  SELECT * FROM mc_demo WHERE b = 500;

• 范围列之后的列无法用于索引定位，只能作为 Filter。

  -- 错误设计！c 会掉进 Filter
  CREATE INDEX idx_bad ON t (a, b_range, c);
  -- 正确：等值列 c 放到 b 之前
  CREATE INDEX idx_good ON t (a, c, b_range);

• OR 条件无法用单个多列索引优化，需要多个单列索引 + BitmapOr。

  -- 不推荐：指望 (a, b) 索引优化这个 OR
  SELECT * FROM t WHERE a = 1 OR b = 2;

• 多列索引列数不宜超过 3 个。列越多，索引越宽，写入代价越高，收益递减。

  -- 不推荐：5 列索引，几乎不会有查询能用上完整前缀
  CREATE INDEX idx_bad ON t (a, b, c, d, e);

• ORDER BY 里的列如果有范围条件干扰，索引无法消除排序。

  -- 索引 (a, b, c)，这句仍会 Sort
  SELECT * FROM t WHERE a = 1 AND b > 2 ORDER BY c;

• 位图扫描会丢失排序信息，Bitmap Heap Scan 之后总有 Sort。

• 多列索引的列顺序不能靠 SQL 书写顺序弥补，改 SQL 写法没用，必须改索引定义。

• INCLUDE 列不参与排序也不参与唯一性，别把它当键列用。

  -- 错误理解：以为 (a) INCLUDE (b) 能优化 WHERE a=? AND b=?
  -- 实际上 b 无法用于索引定位
  CREATE INDEX idx_x ON t (a) INCLUDE (b);

• 跳读扫描（PG 18+）不是银弹，它随最左列基数线性增长开销，别指望它替代正确的列顺序。

• 多列唯一索引和多个单列唯一索引语义完全不同：前者要求组合唯一，后者要求每列各自唯一。

  -- 组合唯一：(t1, alice) 与 (t2, alice) 可以共存
  CREATE UNIQUE INDEX uq ON t (tenant, username);

• indnkeyatts < indnatts 才表示有 INCLUDE 列，用系统表排查时别只看 indnatts。

最佳实践

• 等值列在前、范围列在后、排序列紧贴等值列，这是列顺序的第一原则：

  CREATE INDEX idx_orders ON orders (customer_id, status, created_at DESC);

• 把「会被单独查询的列」放最左，让它一个人也能吃到一个可用的索引前缀：

  -- 如果经常只按 tenant_id 过滤，就把它放最左
  CREATE INDEX idx_logs ON audit_logs (tenant_id, actor_id, created_at DESC);

• 不确定最佳列顺序时，用 EXPLAIN 对比几种候选定义，用真实数据验证：

  CREATE INDEX idx_v1 ON t (a, b, c);
  EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
  DROP INDEX idx_v1;
  CREATE INDEX idx_v2 ON t (a, c, b);
  EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

• 不要为每个查询都建一个多列索引，索引越多写入越慢。合并共前缀的查询到一个索引上：

  -- 同时服务 (tenant_id) 与 (tenant_id, created_at) 两种查询
  CREATE INDEX idx_logs ON audit_logs (tenant_id, created_at);

• 低选择性的列（命中超过 10% 的行）不要单独建索引：

  -- 不推荐：action 只有 5 个值，查询命中 20%
  CREATE INDEX idx_bad ON audit_logs (action);

• 排序需求写进索引定义，用 ASC/DESC/NULLS 精确匹配查询的 ORDER BY：

  CREATE INDEX idx_ranking ON scores (game_id, score DESC NULLS LAST);

• 宽表用 INCLUDE 而不是把只读列塞进键里，避免键过宽、影响排序语义：

  CREATE INDEX idx_orders_cover ON orders (customer_id, created_at DESC) INCLUDE (amount);

• 给多列索引中的每个列考虑 NOT NULL，能减少索引里的 NULL 处理开销，也让优化器估算更准。

• 定期用 pg_stat_user_indexes.idx_scan 复核，删除从未被使用的多列索引：

  SELECT indexrelid::regclass, idx_scan
  FROM pg_stat_user_indexes
  WHERE relname = 'audit_logs' AND idx_scan = 0;

• 大表建多列索引同样用 CONCURRENTLY，但注意它会扫两遍表，需预留更长的维护时间窗：

  CREATE INDEX CONCURRENTLY idx_audit_tenant_actor_created
    ON audit_logs (tenant_id, actor_id, created_at DESC);

下一节

继续学习：唯一索引

参考链接

• PostgreSQL 官方文档：多列索引
• PostgreSQL 官方文档：位图扫描与索引组合
• PostgreSQL 官方文档：CREATE INDEX
• PostgreSQL 官方文档：索引类型
• PostgreSQL 官方文档：索引
• PostgreSQL 官方文档：检查索引使用情况
• PostgreSQL 官方文档：B-Tree 索引实现