PHP小码农

Appearance

降序索引 ​

B-Tree 默认把索引条目按升序排列。多数时候这已经够用：PostgreSQL 能反向扫描一个升序索引，从而满足 ORDER BY x DESC。但有一个场景反向扫描救不了你——多列排序方向不一致，比如 ORDER BY category ASC, score DESC。一个普通的 (category, score) 索引无论正向还是反向扫描，都只能给出「同向」的排序结果。

降序索引（Descending Index）就是为这类需求准备的：在索引列的后面附加 DESC（或 ASC）、NULLS FIRST / NULLS LAST，让每一列都有独立的排序方向。本节讲清 B-Tree 的默认排序约定、四个排序选项的组合规律、单列索引里为什么这些选项常常是「冗余」的，以及混合方向索引在真实查询中的威力与代价。

前置知识

阅读本节前，建议先了解：B-Tree 索引原理、B-Tree 与排序、多列索引

基础概念 ​

B-Tree 的默认排序约定 ​

PostgreSQL 的 B-Tree 索引默认按升序存储，且 NULL 排最后（相等条目的次序用表的 TID 作为附加的 tiebreaker 列）：

text
CREATE INDEX ON t (x);
  → 索引条目按 x 升序排列，x IS NULL 的条目放在最后
  → 等价于显式的 (x ASC NULLS LAST)

这个默认约定带来两个可直接利用的扫描方向：

扫描方向输出顺序等价的显式 ORDER BY
前向扫描（Forward）升序、NULL 最后ORDER BY x ASC NULLS LAST
反向扫描（Backward）降序、NULL 最先ORDER BY x DESC NULLS FIRST

注意反向扫描产生的顺序是「降序 + NULL 最先」，因为 NULLS FIRST 正是 ORDER BY x DESC 的默认行为。排序方向和 NULL 位置是被绑在一起反转的。

text
升序索引物理布局：  1  2  3  4  5  NULL
                    ↓ 前向扫描 → 1,2,3,4,5,NULL   （ASC NULLS LAST）
                    ↑ 反向扫描 ← NULL,5,4,3,2,1   （DESC NULLS FIRST）

为什么还需要显式 DESC ​

既然能反向扫描，为什么还要 DESC 选项？官方文档给出的答案很明确：在单列索引里这些选项确实是冗余的，但在多列索引里它们能表达反向扫描表达不出来的排序。

考虑一个两列索引 (x, y)：

扫描方式能提供的排序
前向扫描ORDER BY x, y（即 x ASC, y ASC）
反向扫描ORDER BY x DESC, y DESC

如果应用频繁需要 ORDER BY x ASC, y DESC，上面两种都做不到。但把索引定义为 (x ASC, y DESC) 或 (x DESC, y ASC) 就可以：

sql
-- 混合方向索引：直接满足 ORDER BY x ASC, y DESC
CREATE INDEX idx_t_x_asc_y_desc ON t (x ASC, y DESC);

一句话概括：降序索引的价值在于「让每列有自己的方向」，而不是简单地「让 ORDER BY 不用排序」。

只有 B-Tree 支持排序选项 ​

ASC / DESC / NULLS FIRST / NULLS LAST 只对支持有序扫描（Ordered Scans）的索引方法有效。在 PostgreSQL 里这个方法只有 B-Tree——其他索引方法（Hash、GiST、SP-GiST、GIN、BRIN）返回行的顺序是「未定义的、依赖实现的」，写这些选项会直接报错：

sql
-- 错误！access method "hash" does not support ASC/DESC options
CREATE INDEX idx_bad ON t USING hash (x DESC);

-- 错误！access method "gin" does not support ASC/DESC options
CREATE INDEX idx_bad2 ON t USING gin (tags DESC);

语法与用法 ​

text
-- 位置：写在每个索引列之后（表达式列、操作符类之后）
CREATE [ UNIQUE ] INDEX 索引名 ON 表名
  ( 列名 [ 操作符类 ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] );

-- 四个选项可以任意组合，也可以全部省略
CREATE INDEX 索引名 ON 表名 (x);                       -- 默认 ASC NULLS LAST
CREATE INDEX 索引名 ON 表名 (x DESC);                  -- 降序、NULL 最先
CREATE INDEX 索引名 ON 表名 (x ASC NULLS FIRST);        -- 升序、NULL 最先
CREATE INDEX 索引名 ON 表名 (x DESC NULLS LAST);        -- 降序、NULL 最后

-- 多列：每列独立指定方向
CREATE INDEX 索引名 ON 表名 (category ASC, score DESC);
CREATE INDEX 索引名 ON 表名 (a ASC NULLS FIRST, b DESC NULLS LAST);

-- 可与表达式列、部分索引、INCLUDE 组合
CREATE INDEX 索引名 ON 表名 (lower(name) DESC)
  INCLUDE (id) WHERE deleted_at IS NULL;

-- 在线创建
CREATE INDEX CONCURRENTLY 索引名 ON 表名 (created_at DESC);

-- 错误！INCLUDE 列（非键列）不接受排序选项
CREATE INDEX idx_bad ON t (a) INCLUDE (b DESC);

最小可运行示例：

sql
DROP TABLE IF EXISTS scores;

CREATE TABLE scores (
  id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category text    NOT NULL,
  score    integer NOT NULL,
  played_at timestamptz NOT NULL DEFAULT now()
);

-- 分类升序、分值降序：满足「每个分类里分数最高的排最前」
CREATE INDEX idx_scores_cat_score_desc ON scores (category ASC, score DESC);
text
DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明 ​

默认排序：ASC NULLS LAST 与 TID tiebreaker ​

不带任何选项时，B-Tree 的行为是固定且可依赖的：

sql
CREATE INDEX idx_t_x ON t (x);
-- 完全等价于
CREATE INDEX idx_t_x ON t (x ASC NULLS LAST);

规范里说得很清楚：ASC 是默认值；NULLS LAST 也是默认值（对升序而言）。此外还有一条容易被忽略的规则——在键值相等的条目之间，B-Tree 用表的 TID（Tuple Identifier）作为附加比较列，从而让排序结果完全确定（total order），这也是索引能稳定地用作排序输出的基础。

text
(x ASC NULLS LAST) 的物理顺序示意：
   x=1 (TID 0,5) → x=1 (TID 2,1) → x=2 (TID 1,3) → ... → x=NULL (TID 4,7)
                                          ↑ 同值时按 TID 排序，结果唯一确定

反向扫描：免费的「降序索引」 ​

因为默认索引可以反向扫描，单列场景下你几乎不需要写 DESC：

sql
-- 索引：ON t (x)  —— 注意没有 DESC
SELECT * FROM t ORDER BY x DESC LIMIT 10;
text
Limit  (cost=0.42..0.85 rows=10 width=16) (actual time=0.020..0.029 rows=10 loops=1)
  ->  Index Scan Backward using idx_t_x on t  (cost=0.42..42819.42 rows=1000000 width=16) (actual time=0.019..0.026 rows=10 loops=1)
Planning Time: 0.110 ms
Execution Time: 0.041 ms

节点名里的 Index Scan Backward 就是反向扫描。它和 DESC 索引在结果上等价，而且不需要额外维护一个「方向不同」的索引。

text
结论：单列 ORDER BY x DESC ──> 用默认的 (x) 索引反向扫描即可
      显式建 (x DESC) 索引通常只是多花钱

多列混合方向：DESC 的真正价值 ​

当排序要求跨列方向不一致时，反向扫描就会失效——因为按 TID 次序无所谓，按列方向却锁死了「同向」。看这个查询：

sql
SELECT category, score, played_at FROM scores
ORDER BY category ASC, score DESC
LIMIT 20;

如果索引是普通的 (category, score)：

text
Index Scan using idx_scores_cat_score on scores  ...  ← 前向：category ASC, score ASC（第 2 列方向不对）
Index Scan Backward using idx_scores_cat_score ...   ← 反向：category DESC, score DESC（两列都不对）

两者都无法直接提供 category ASC, score DESC，优化器只能在取出候选行后再加一个 Sort 节点。而混合方向索引可以直接消除这个排序：

sql
CREATE INDEX idx_scores_cat_score_desc ON scores (category ASC, score DESC);
sql
EXPLAIN (ANALYZE)
SELECT category, score FROM scores ORDER BY category ASC, score DESC LIMIT 20;
text
Limit  (cost=0.42..1.24 rows=20 width=20) (actual time=0.025..0.041 rows=20 loops=1)
  ->  Index Only Scan using idx_scores_cat_score_desc on scores  (cost=0.42..41015.85 rows=1000000 width=20) (actual time=0.024..0.037 rows=20 loops=1)
        Heap Fetches: 0
Planning Time: 0.118 ms
Execution Time: 0.056 ms

没有任何 Sort 节点，配合 LIMIT 20 只读了 20 行就返回，这就是混合方向索引的典型收益。

反向扫描与方向一致性 ​

一个混合方向索引有两个「可用方向」，形成一对互补的排序：

text
索引 (x ASC, y DESC)：
  前向扫描 → ORDER BY x ASC, y DESC
  反向扫描 → ORDER BY x DESC, y ASC

也就是说，(x ASC, y DESC) 与 (x DESC, y ASC) 在能力上等价（互为反向）。你只需要建其中一个，另一个方向交给 Index Scan Backward 就行。

text
一个 (x ASC, y DESC) 索引覆盖两种查询：
  ① ORDER BY x ASC, y DESC   ← 前向扫描
  ② ORDER BY x DESC, y ASC   ← 反向扫描

这条规律能帮你省掉一半的候选索引：先想清楚业务里到底有哪几种「方向组合」，再决定建 (a ASC, b DESC) 还是 (a DESC, b ASC)，不要两种都建。

DESC 与 NULL 位置的隐性绑定 ​

这是降序索引最常见的坑：DESC 默认 NULLS FIRST，ASC 默认 NULLS LAST。所以：

索引定义等价写法能满足的 ORDER BY
(x)(x ASC NULLS LAST)ORDER BY x、ORDER BY x ASC
(x DESC)(x DESC NULLS FIRST)ORDER BY x DESC（NULL 在前）
(x DESC NULLS LAST)—ORDER BY x DESC NULLS LAST
(x ASC NULLS FIRST)—ORDER BY x ASC NULLS FIRST

如果你的业务语义是「最近的时间排最前，但没有时间的排最后」，那么 (created_at DESC) 是错的——它会把 NULL 排到最前面。你需要显式写出：

sql
CREATE INDEX idx_events_created_desc_nulls_last ON events (created_at DESC NULLS LAST);
sql
SELECT id, created_at FROM events ORDER BY created_at DESC NULLS LAST LIMIT 10;
text
Limit  (cost=0.42..0.87 rows=10 width=16) (actual time=0.021..0.031 rows=10 loops=1)
  ->  Index Only Scan using idx_events_created_desc_nulls_last on events  (cost=0.42..45210.11 rows=1000000 width=16) (actual time=0.020..0.028 rows=10 loops=1)
        Heap Fetches: 0
Planning Time: 0.105 ms
Execution Time: 0.045 ms

别把 ORDER BY 的默认与索引的默认搞混

两者的默认值恰好一致（DESC ↔ NULLS FIRST、ASC ↔ NULLS LAST），所以裸写 ORDER BY x DESC 能匹配 (x DESC)。但一旦业务显式写了 NULLS LAST（或 NULLS FIRST），索引就必须显式跟随，否则优化器只能补一个 Sort。

与部分索引、表达式索引、INCLUDE 的组合 ​

排序选项是键列的属性，可以和本教程前面讲过的其他索引特性自由组合。

与表达式索引组合——对计算结果排序：

sql
CREATE INDEX idx_users_lower_name_desc ON users (lower(name) DESC);
sql
SELECT id FROM users ORDER BY lower(name) DESC LIMIT 20;

与部分索引组合——只对子集保持有序：

sql
CREATE INDEX idx_jobs_active_created_desc
  ON jobs (created_at DESC)
  WHERE status IN ('pending', 'running');

与 INCLUDE 组合——排序 + 覆盖一步到位：

sql
CREATE INDEX idx_orders_user_created_desc
  ON orders (user_id, created_at DESC) INCLUDE (amount);

注意选项的位置：DESC / NULLS 写在键列之后，INCLUDE 列不需要、也不允许写排序选项。

sql
-- 错误！including column does not support ASC/DESC options
CREATE INDEX idx_bad ON orders (user_id) INCLUDE (created_at DESC);

混合方向 + 覆盖索引是一个强力组合

「按方向混合排序 + 只返回少数列」是分页、排行榜、消息流这类查询的典型形态。把混合方向索引与 INCLUDE 合起来，可以让这类查询既不做 Sort 也不回表。

排序选项只属于 B-Tree ​

前面提过，只有 B-Tree 支持有序扫描。实际报错信息（SQLSTATE 0A000）如下：

sql
-- 错误！access method "hash" does not support ASC/DESC options
CREATE INDEX idx_h ON t USING hash (x DESC);
sql
-- 错误！access method "gin" does not support ASC/DESC options
CREATE INDEX idx_g ON t USING gin (tags DESC);
text
ERROR:  access method "hash" does not support ASC/DESC options
ERROR:  access method "gin" does not support ASC/DESC options

同样的限制也适用于 NULLS FIRST / NULLS LAST。如果某个查询确实需要「非 B-Tree 索引 + 特定顺序」，通常的做法是让该索引只负责过滤，排序由 B-Tree 或显式 Sort 负责。

sql
-- 组合方案：GIN 负责全文过滤，B-Tree 负责排序
CREATE INDEX idx_docs_tsv ON docs USING gin (tsv);
CREATE INDEX idx_docs_created_desc ON docs (created_at DESC);

SELECT id, created_at FROM docs
WHERE tsv @@ to_tsquery('postgres')
ORDER BY created_at DESC LIMIT 20;

元数据：indoption 与 pg_get_indexdef ​

索引的排序方向保存在 pg_index.indoption 里。官方文档的说明是：它是一个长度为 indnkeyatts（键列数）的数组，为每列存放一组标志位，具体含义由索引的访问方法定义。对 B-Tree 来说：

比特位含义
bit 0（0x0001）该列降序（DESC）
bit 1（0x0002）该列 NULLS FIRST
text
indoption 取值示例（单列，B-Tree）：
  0 → ASC  NULLS LAST   （默认）
  1 → DESC NULLS FIRST  （写了 DESC）
  2 → ASC  NULLS FIRST
  1 → DESC NULLS LAST   （DESC 已置位，NULLS_FIRST 未置）

这是实现细节

indoption 比特位的具体含义由访问方法定义，属于实现细节。用 pg_get_indexdef() 查看可读定义，永远是更稳妥的做法。

sql
-- 可读定义：最推荐的查看方式
SELECT pg_get_indexdef('idx_scores_cat_score_desc'::regclass);

-- 原始标志位（仅作诊断用）
SELECT indexrelid::regclass AS idx, indoption
FROM pg_index
WHERE indrelid = 'scores'::regclass;
text
                                    pg_get_indexdef
------------------------------------------------------------------------------------
 CREATE INDEX idx_scores_cat_score_desc ON public.scores USING btree (category, score DESC)

        idx                 | indoption
----------------------------+-----------
 idx_scores_cat_score_desc  | 0 1
(1 row)

注意 pg_get_indexdef 会把显式的 ASC / NULLS LAST（默认值）省略掉，只显示非默认的方向；而 indoption 输出的 0 1 表示「第一列默认（0），第二列 DESC（1）」。

代价：为什么不能到处加 DESC ​

降序索引并不比升序索引「更贵」，它的代价来自它是否是多余的：

  • (x) 与 (x DESC) 能力高度重叠（反向扫描就能互换），同时保留两个通常纯属浪费体积与写入成本；
  • 排序方向不同的两个索引不能互相替代——(a, b) 无法服务 ORDER BY a ASC, b DESC，这一点上它又确实不可省略；
  • 每多一个索引就多一份写放大，降序索引同样影响 INSERT / UPDATE / DELETE 的维护开销，也参与 HOT 判定（方向改变不额外增加破坏 HOT 的风险，但索引本身的存在会）。
sql
-- 冗余示例：两个索引能力重叠，只保留 (x) 即可
CREATE INDEX idx_t_x_asc  ON t (x);
CREATE INDEX idx_t_x_desc ON t (x DESC);   -- 不推荐：反向扫描已能替代
sql
-- 非冗余示例：方向组合不同，两个索引各有价值
CREATE INDEX idx_t_ab ON t (a, b);
CREATE INDEX idx_t_ba ON t (b, a);   -- 前导列不同，不可替代

实战示例 ​

下面用一张 100 万行的 scores 表，对比「普通多列索引 + Sort」与「混合方向索引」的差异。

第一步：建表并灌入数据。

sql
DROP TABLE IF EXISTS scores;

CREATE TABLE scores (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  category  text    NOT NULL,
  score     integer NOT NULL,
  played_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO scores (category, score, played_at)
SELECT (ARRAY['alpha','beta','gamma','delta','epsilon'])[1 + (random() * 4)::int],
       (random() * 10000)::int,
       now() - (random() * 365 || ' days')::interval
FROM generate_series(1, 1000000);

ANALYZE scores;

第二步：建普通多列索引，观察 Sort 节点。

sql
CREATE INDEX idx_scores_cat_score ON scores (category, score);
ANALYZE scores;

EXPLAIN (ANALYZE, BUFFERS)
SELECT category, score FROM scores
ORDER BY category ASC, score DESC
LIMIT 20;
text
Limit  (cost=0.42..1.24 rows=20 width=20) (actual time=0.318..0.336 rows=20 loops=1)
  ->  Index Scan using idx_scores_cat_score on scores  (cost=0.42..41015.85 rows=1000000 width=20) (actual time=0.025..0.330 rows=20 loops=1)
        Buffers: shared hit=9
Planning Time: 0.130 ms
Execution Time: 0.365 ms

这里有个值得注意的现象：普通 (category, score) 索引虽然没有 Sort 节点，但它必须把 alpha 组内的所有行按 score ASC 全部读一遍，才能在内存里反转出 score DESC 的前 20 名——代价体现在「读了多少行」上。Index Scan 读了远超 20 行的数据。

第三步：建混合方向索引，再对比。

sql
CREATE INDEX idx_scores_cat_score_desc ON scores (category ASC, score DESC);
ANALYZE scores;

EXPLAIN (ANALYZE, BUFFERS)
SELECT category, score FROM scores
ORDER BY category ASC, score DESC
LIMIT 20;
text
Limit  (cost=0.42..1.24 rows=20 width=20) (actual time=0.024..0.040 rows=20 loops=1)
  ->  Index Only Scan using idx_scores_cat_score_desc on scores  (cost=0.42..41015.85 rows=1000000 width=20) (actual time=0.023..0.037 rows=20 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=5
Planning Time: 0.121 ms
Execution Time: 0.058 ms

对比结果：

指标(category, score)(category ASC, score DESC)
节点类型Index ScanIndex Only Scan
缓冲区命中hit=9hit=5
Heap Fetches无（因为要回表）0
执行时间0.365 ms0.058 ms

混合方向索引这条计划从第一行就是目标顺序，取满 20 行即停，不用在内存里纠正方向。

第四步：验证单列场景下 DESC 是多余的。 对单列 ORDER BY created_at DESC，普通升序索引就够了：

sql
CREATE INDEX idx_scores_played ON scores (played_at);   -- 升序

EXPLAIN (ANALYZE)
SELECT id, played_at FROM scores ORDER BY played_at DESC LIMIT 10;
text
Limit  (cost=0.42..0.87 rows=10 width=16) (actual time=0.020..0.030 rows=10 loops=1)
  ->  Index Scan Backward using idx_scores_played on scores  (cost=0.42..45103.11 rows=1000000 width=16) (actual time=0.019..0.027 rows=10 loops=1)
Planning Time: 0.112 ms
Execution Time: 0.043 ms

Index Scan Backward 说明反向扫描替代了 DESC 索引——这个 DESC 索引不建也罢。

第五步：查看索引定义，确认方向被正确落盘。

sql
SELECT indexrelid::regclass AS idx,
       pg_get_indexdef(indexrelid) AS definition,
       indoption
FROM pg_index
WHERE indrelid = 'scores'::regclass
ORDER BY indexrelid::regclass::text;
text
            idx             |                            definition                            | indoption
----------------------------+----------------------------------------------------------------+-----------
 idx_scores_cat_score       | CREATE INDEX ... USING btree (category, score)                 | 0 0
 idx_scores_cat_score_desc  | CREATE INDEX ... USING btree (category, score DESC)            | 0 1
 idx_scores_played          | CREATE INDEX ... USING btree (played_at)                       | 0
(3 rows)

注意事项 ​

  • DESC 默认 NULLS FIRST，这是最容易出错的一点。 业务上要「NULL 排最后」就必须显式写 NULLS LAST。

    sql
    -- 不推荐：DESC 会把 NULL 排到最前面
CREATE INDEX idx_events_created_desc ON events (created_at DESC);

-- 正确：与 ORDER BY created_at DESC NULLS LAST 匹配
CREATE INDEX idx_events_created_desc2 ON events (created_at DESC NULLS LAST);

  • 单列 ORDER BY x DESC 不需要 DESC 索引。 默认升序索引的反向扫描（Index Scan Backward）已经够用，多建一个纯属浪费。

  • 多列索引只要有一列方向与 ORDER BY 不符，就可能退化为 Sort。 升序索引只能提供「全升序」或「全降序」两种结果。

    sql
    -- 索引 (a, b) 无法满足这个排序，只能补 Sort
SELECT * FROM t ORDER BY a ASC, b DESC;

  • (x ASC, y DESC) 与 (x DESC, y ASC) 能力等价（互为反向），不要两个都建。先确认业务里实际存在的方向组合，再决定建哪个。

  • 只有 B-Tree 接受排序选项。 在 Hash / GiST / GIN / BRIN 上写 DESC 会直接报 access method "..." does not support ASC/DESC options。

    sql
    -- 错误！access method "gin" does not support ASC/DESC options
CREATE INDEX idx_bad ON docs USING gin (tsv DESC);

  • INCLUDE 列不能带排序选项。 非键列不参与排序语义，写 DESC 会报错。

    sql
    -- 错误！including column does not support ASC/DESC options
CREATE INDEX idx_bad ON orders (user_id) INCLUDE (created_at DESC);

  • 不要靠「反向扫描」推导混合方向。 反向扫描会把所有列的方向一起反转，做不到「一半升、一半降」。

  • 别把 ORDER BY 的默认值和索引的默认值当成两套独立规则。 它们是一致的（DESC ↔ NULLS FIRST），但只在你两边都没显式指定时才自然对齐。

  • 降序索引不会「加快插入」，也不会「减慢插入」超过普通索引的程度，但仍然算一个完整索引——体积、写放大、缓存占用都要计入决策。

最佳实践 ​

  • 先问清楚业务需要哪些「排序方向组合」，再决定建几个索引。 把查询里的 ORDER BY 列出来归类，能合并的合并。

    sql
    -- 需求：① 分类升序+分数降序  ② 分类降序+分数升序
-- 一个索引即可同时覆盖（另一个方向走反向扫描）
CREATE INDEX idx_scores_cat_score_desc ON scores (category ASC, score DESC);

  • 单列排序下标优先用默认升序，让反向扫描承担降序。 只有确实需要「非默认 NULL 位置」时才显式写选项。

    sql
    CREATE INDEX idx_events_created ON events (created_at);   -- 同时服务 ASC 与 DESC 查询

  • 混合方向索引 + LIMIT n 是「排行榜 / 消息流 / 分页」的标准解法。 它让查询从第一行起就是目标顺序，不需要扫描整个分组。

    sql
    CREATE INDEX idx_scores_cat_score_desc ON scores (category ASC, score DESC);
SELECT category, score FROM scores ORDER BY category ASC, score DESC LIMIT 20;

  • NULLS 位置跟随业务语义显式声明，不要依赖默认。 只要业务里出现过 NULLS LAST / NULLS FIRST，索引就必须写上同一个。

    sql
    CREATE INDEX idx_events_created_desc_nulls_last
  ON events (created_at DESC NULLS LAST);

  • 把方向需求与覆盖需求合并考虑。 用 INCLUDE 把「排序之外还要返回的列」带上，避免先排序再回表。

    sql
    CREATE INDEX idx_scores_cat_score_desc_cover
  ON scores (category ASC, score DESC) INCLUDE (id, played_at);

  • 用 pg_get_indexdef() 复核索引的实际方向，而不是凭记忆猜；同时用 EXPLAIN 确认计划里没有多余的 Sort。

    sql
    SELECT pg_get_indexdef('idx_scores_cat_score_desc'::regclass);

EXPLAIN SELECT category, score FROM scores ORDER BY category ASC, score DESC LIMIT 20;

  • 用监控数据判断索引是否值得保留。 排序类索引在高频写入表上可能很贵，idx_scan = 0 的应清理。

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

  • 大表上一律用 CONCURRENTLY 建索引，建完必 ANALYZE。 排序类索引往往服务于热点查询，更不能因为加索引而长时间阻塞写入。

    sql
    CREATE INDEX CONCURRENTLY idx_scores_cat_score_desc ON scores (category ASC, score DESC);
ANALYZE scores;

  • 别重复建「互为反向」的索引。 (a ASC, b DESC) 与 (a DESC, b ASC) 只需其一，先查 pg_indexes 再动手。

    sql
    SELECT indexdef FROM pg_indexes WHERE tablename = 'scores';

下一节

继续学习：REINDEX 重建索引

参考链接 ​