索引排序与 NULLS FIRST / LAST

优化查询性能最有效的手段之一，往往不是「找到更少的行」，而是「不排序」。当数据量达到百万级，ORDER BY ... LIMIT 20 里的 Sort 节点可能比全表扫描还贵——它要么把所有行排序后取前 20 条，要么维护一个 top-N 堆。

B-Tree 是唯一能直接输出有序结果的索引类型。只要索引的顺序与 ORDER BY 匹配，优化器就能把 Sort 节点整个消掉，直接按索引序取前 N 条返回。本节讲清索引的物理排序规则、ASC/DESC 与 NULLS FIRST/LAST 的组合、多列混合方向的限制、反向扫描、collation 的影响，以及如何用 EXPLAIN 判断 Sort 是否真的被消除。

前置知识

阅读本节前，建议先了解：唯一索引

基础概念

索引的物理排序

B-Tree 的叶子页按键值有序串联成双向链表：

索引 (score ASC) 的叶子页：

 ┌──────────────┐   ┌──────────────┐   ┌──────────────┐
 │ 10  20  30   │ ⇄ │ 40  50  60   │ ⇄ │ 70  80  90   │
 └──────────────┘   └──────────────┘   └──────────────┘
       正向扫描 ────────────────────────────────────────►
                                       ◄──────────────── 反向扫描

因此：

• 正向扫描（forward scan）：从小到大遍历
• 反向扫描（backward scan）：从大到小遍历

两者都不需要额外排序，都只按链表的物理顺序读。

默认排序方向与 NULL 位置

B-Tree 每一列的默认排序规则来自 SQL 标准：

写法	等价于	说明
(col)	(col ASC NULLS LAST)	升序，NULL 在最后
(col ASC)	(col ASC NULLS LAST)	同上
(col DESC)	(col DESC NULLS FIRST)	降序，NULL 在最前

这张表是本节最需要记住的内容——ASC 的默认 NULL 位置是 LAST，而 DESC 的默认 NULL 位置是 FIRST，两者相反。

ORDER BY 的默认语义

查询里的 ORDER BY 也有默认值：

查询写法	等价于
ORDER BY col	ORDER BY col ASC NULLS LAST
ORDER BY col ASC	ORDER BY col ASC NULLS LAST
ORDER BY col DESC	ORDER BY col DESC NULLS FIRST

所以 ORDER BY col 和索引 (col) 的默认排序完全一致——这是最常见、最容易命中的情况。

正扫与反扫分别满足什么

索引定义	正向扫描满足	反向扫描满足
(col) = (col ASC NULLS LAST)	ORDER BY col	ORDER BY col DESC NULLS FIRST
(col DESC NULLS FIRST)	ORDER BY col DESC	ORDER BY col
(col NULLS FIRST)	ORDER BY col NULLS FIRST	ORDER BY col DESC NULLS LAST
(col DESC NULLS LAST)	ORDER BY col DESC NULLS LAST	ORDER BY col NULLS FIRST

规律：反向扫描会把「顺序方向」和「NULL 位置」同时翻转。所以一个方向的定义能覆盖两种查询，但被覆盖的那一种是「另一个方向 + 另一个 NULL 位置」的组合，而不是它自己的默认值。

多列索引的排序能力

多列索引 (a, b) 的排序是字典序：先按 a，a 相同再按 b。

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

关键限制：ORDER BY a ASC, b DESC 不能用 (a, b) 索引满足，必须建 (a ASC, b DESC) 或 (a DESC, b ASC)。这就是「混合方向」问题。

排序被索引满足的完整条件

优化器消除 Sort 需要同时满足：

1. ORDER BY 的列全部来自同一个索引（或可拼接）
2. 列的顺序与索引的前缀一致
3. 每列的方向与 NULL 位置与索引定义匹配（或整体反向）
4. 前面的列必须是「等值条件」（否则顺序会中断）

第 4 条最容易忽略：

索引 (status, created_at)

WHERE status = 'paid' ORDER BY created_at   ✓ 消 Sort
WHERE status > 'a'    ORDER BY created_at   ✗ 有 Sort（status 是范围，created_at 顺序重置）

分页查询：索引排序的主战场

ORDER BY ... LIMIT n 是排序优化收益最大的场景，因为：

方案	复杂度	需要处理的行数
顺序扫描 + Sort + Limit	O(N log N)	全部 N 行
索引有序扫描 + Limit	O(k)	只需读前 k 条

ORDER BY created_at DESC LIMIT 20，表有 1000 万行

无索引：Seq Scan（1000 万行）→ Sort → Limit 20
有索引：(created_at DESC)：Index Scan → Limit 20（读 20 条就停）

这就是「排行榜 / 最新列表」类查询在索引排序后能从秒级降到毫秒级的原因。

反向扫描不是免费的

反向扫描虽然不排序，但有两个隐含代价：

代价	说明
随机 I/O	比正向扫描更可能触发预读失效（尤其机械盘）
B-Tree 内部开销	反向遍历页链表需要读到页尾，实现上略复杂

另一个重要区别是位图扫描不支持反向：位图扫描按物理页序回表，顺序信息全丢。所以一旦计划里出现 Bitmap Heap Scan，ORDER BY 一定要额外排序。

collation 对排序索引的影响

文本列的排序不是「按字节」而是「按 locale 规则」，这直接影响索引能否提供排序：

collation = 'C'：按字节比较，'A' < 'B' < 'a' < 'b'
collation = 'en_US'：按语言规则，'a' < 'A' < 'b' < 'B'

三条要点：

1. 一个索引列只能承载一种 collation，需要多种 collation 就得多建索引。
2. 查询的 collation 必须与索引一致，否则索引排序不被认可。
3. 非确定性（nondeterministic）collation 会让排序去重、索引失效。

语法与用法

-- 默认：升序，NULL 在最后
CREATE INDEX 索引名 ON 表名 (列名);

-- 显式升序 + NULL 位置
CREATE INDEX 索引名 ON 表名 (列名 ASC NULLS LAST);

-- 降序，NULL 在最前（DESC 的默认）
CREATE INDEX 索引名 ON 表名 (列名 DESC);

-- 降序 + NULL 在最后
CREATE INDEX 索引名 ON 表名 (列名 DESC NULLS LAST);

-- 多列混合方向
CREATE INDEX 索引名 ON 表名 (列1 ASC, 列2 DESC);

-- 指定 collation（文本列排序）
CREATE INDEX 索引名 ON 表名 (列名 COLLATE "C");

-- 表达式 + 排序
CREATE INDEX 索引名 ON 表名 ((lower(列名)) DESC);

-- 部分索引 + 排序
CREATE INDEX 索引名 ON 表名 (列名 DESC) WHERE 条件;

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

最小可运行示例：

DROP TABLE IF EXISTS demo_sort;

CREATE TABLE demo_sort (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  score integer
);

CREATE INDEX idx_demo_sort_score_desc ON demo_sort (score DESC);

DROP TABLE
CREATE TABLE
CREATE INDEX

详细说明

默认 NULL 位置实测

先验证 ASC 默认 NULLS LAST、DESC 默认 NULLS FIRST：

DROP TABLE IF EXISTS null_demo;

CREATE TABLE null_demo (
  id    integer PRIMARY KEY,
  info  integer
);

INSERT INTO null_demo (id, info) VALUES
  (1, 3), (2, NULL), (3, 1), (4, NULL), (5, 2);

DROP TABLE
CREATE TABLE
INSERT 0 5

升序查询（NULL 在最后）：

SELECT id, info FROM null_demo ORDER BY info ASC;

 id | info
----+------
  3 |    1
  5 |    2
  1 |    3
  2 |
  4 |
(5 rows)

降序查询（NULL 在最前）：

SELECT id, info FROM null_demo ORDER BY info DESC;

 id | info
----+------
  2 |
  4 |
  1 |    3
  5 |    2
  3 |    1
(5 rows)

显式指定后的效果：

SELECT id, info FROM null_demo ORDER BY info ASC NULLS FIRST;

 id | info
----+------
  2 |
  4 |
  3 |    1
  5 |    2
  1 |    3
(5 rows)

单列索引如何消除 Sort

CREATE INDEX idx_null_demo_info ON null_demo (info);
ANALYZE null_demo;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM null_demo ORDER BY info LIMIT 3;

                                QUERY PLAN
---------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Index Only Scan using idx_null_demo_info on null_demo (actual rows=3 loops=1)
         Heap Fetches: 0
(3 rows)

没有 Sort 节点。对照一个「用不上索引排序」的查询（方向 + NULL 位置都不匹配）：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM null_demo ORDER BY info DESC NULLS LAST LIMIT 3;

                                   QUERY PLAN
-------------------------------------------------------------------------------
 Limit (actual rows=3 loops=1)
   ->  Sort (actual rows=3 loops=1)
         Sort Key: info DESC NULLS LAST
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Only Scan using idx_null_demo_info on null_demo
(4 rows)

Sort 出现了。为什么？因为索引 (info) 反向扫描对应的是 ORDER BY info DESC NULLS FIRST，而这里是 NULLS LAST，不匹配，所以必须重排。

反向扫描：ORDER BY x DESC 为什么也能用索引

一个常见误解是「索引是升序的，所以 DESC 用不了」。事实相反：

DROP TABLE IF EXISTS sales CASCADE;

CREATE TABLE sales (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(10,2) NOT NULL,
  sold_at timestamptz NOT NULL
);

INSERT INTO sales (amount, sold_at)
SELECT (random()*1000)::numeric(10,2), now() - (g || ' minutes')::interval
FROM generate_series(1, 200000) AS g;

CREATE INDEX idx_sales_sold_at ON sales (sold_at);
ANALYZE sales;

DROP TABLE
CREATE TABLE
INSERT 0 200000
CREATE INDEX
ANALYZE

索引定义为默认升序，但查询要倒序：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, sold_at FROM sales ORDER BY sold_at DESC LIMIT 5;

                                        QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual rows=5 loops=1)
   ->  Index Only Scan Backward using idx_sales_sold_at on sales (actual rows=5 loops=1)
         Heap Fetches: 0
(3 rows)

计划里明确写着 Index Scan Backward——反向扫描，不需要 Sort。

DESC 索引的适用场景

既然反向扫描能覆盖 DESC，为什么还要建降序索引？答案是多列或混合方向：

-- 查询：按 status 分组内的金额从大到小
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount FROM sales
WHERE sold_at >= now() - interval '1 day'
ORDER BY sold_at ASC, amount DESC
LIMIT 10;

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Sort (actual rows=10 loops=1)
         Sort Key: sold_at, amount DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Index Scan using idx_sales_sold_at on sales
               Index Cond: (sold_at >= (now() - '1 day'::interval))
(5 rows)

amount 没有被索引，只能排。建一个混合方向的多列索引：

CREATE INDEX idx_sales_sold_at_amount_desc
  ON sales (sold_at ASC, amount DESC);
ANALYZE sales;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, amount FROM sales
WHERE sold_at >= now() - interval '1 day'
ORDER BY sold_at ASC, amount DESC
LIMIT 10;

                                        QUERY PLAN
-------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Index Scan using idx_sales_sold_at_amount_desc on sales (actual rows=10 loops=1)
         Index Cond: (sold_at >= (now() - '1 day'::interval))
(3 rows)

Sort 消失。混合方向必须显式写进索引定义，这是降序索引存在的唯一理由。

反向扫描只能同时翻转所有列

INDEX (a, b) 只能提供 (a, b) 的正序和 (a DESC, b DESC) 的反序。它给不出 (a ASC, b DESC) 或 (a DESC, b ASC)。这两种必须专门建索引。

多列索引的排序与「等值列」前提

多列索引提供排序的前提是排序列之前的所有列都是等值条件：

DROP TABLE IF EXISTS tasks;

CREATE TABLE tasks (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  project_id integer NOT NULL,
  status     text NOT NULL,
  priority   integer NOT NULL,
  created_at timestamptz NOT NULL
);

INSERT INTO tasks (project_id, status, priority, created_at)
SELECT
  (random()*100)::int,
  (ARRAY['todo','doing','done'])[1 + (g % 3)],
  (random()*5)::int,
  now() - (g || ' seconds')::interval
FROM generate_series(1, 300000) AS g;

CREATE INDEX idx_tasks_project_created
  ON tasks (project_id, created_at DESC);
ANALYZE tasks;

DROP TABLE
CREATE TABLE
INSERT 0 300000
CREATE INDEX
ANALYZE

project_id 等值 + 按 created_at 排序：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM tasks
WHERE project_id = 7
ORDER BY created_at DESC
LIMIT 10;

                                       QUERY PLAN
------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Index Scan using idx_tasks_project_created on tasks (actual rows=10 loops=1)
         Index Cond: (project_id = 7)
(3 rows)

把 project_id 改成范围条件，排序立刻失效：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM tasks
WHERE project_id > 7
ORDER BY created_at DESC
LIMIT 10;

                                          QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Sort (actual rows=10 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 25kB
         ->  Bitmap Heap Scan on tasks
               Recheck Cond: (project_id > 7)
               ->  Bitmap Index Scan on idx_tasks_project_created
                     Index Cond: (project_id > 7)
(8 rows)

project_id 是范围时，每个不同 project_id 下的 created_at 顺序是独立的，合起来并不全局有序，所以必须 Sort。

NULLS FIRST 索引的实战用途

有些业务希望「空值排在最前面」（如「待处理」优先展示）：

DROP TABLE IF EXISTS tickets;

CREATE TABLE tickets (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title      text NOT NULL,
  assignee   text,
  created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO tickets (title, assignee)
SELECT 'T-' || g, CASE WHEN g % 3 = 0 THEN NULL ELSE 'user-' || (g % 20) END
FROM generate_series(1, 100000) AS g;

-- 未分配的排在最前
CREATE INDEX idx_tickets_assignee_nulls_first
  ON tickets (assignee NULLS FIRST);
ANALYZE tickets;

DROP TABLE
CREATE TABLE
INSERT 0 100000
CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, assignee FROM tickets
ORDER BY assignee NULLS FIRST
LIMIT 10;

                                            QUERY PLAN
---------------------------------------------------------------------------------------------------
 Limit (actual rows=10 loops=1)
   ->  Index Only Scan using idx_tickets_assignee_nulls_first on tickets (actual rows=10 loops=1)
         Heap Fetches: 0
(3 rows)

未分配的工单（assignee IS NULL）排在最前面，且没有 Sort。

分页：OFFSET 的陷阱与键集分页

索引排序让分页变快，但 OFFSET 仍会带来线性退化：

-- 第 1 页：很快
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, sold_at FROM sales ORDER BY sold_at DESC LIMIT 20;

-- 第 5000 页：OFFSET 越大越慢
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, sold_at FROM sales ORDER BY sold_at DESC LIMIT 20 OFFSET 100000;

                                         QUERY PLAN
------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan Backward using idx_sales_sold_at on sales (actual rows=100020 loops=1)
         Heap Fetches: 0
(3 rows)

注意 actual rows=100020——索引必须跳过前 10 万条才能返回目标 20 条。解决方案是键集分页（keyset pagination）：

-- 上一页最后一条的 sold_at 是 '2026-09-01 12:00:00+08'
SELECT id, sold_at FROM sales
WHERE sold_at < '2026-09-01 12:00:00+08'
ORDER BY sold_at DESC
LIMIT 20;

                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan Backward using idx_sales_sold_at on sales (actual rows=20 loops=1)
         Index Cond: (sold_at < '2026-09-01 12:00:00+08'::timestamp with time zone)
         Heap Fetches: 0
(4 rows)

actual rows=20——无论翻到第几页，都只读 20 条。这是索引排序最有价值的用法。

collation 与排序索引

文本列的排序依赖 collation，索引必须与查询使用同一个 collation：

DROP TABLE IF EXISTS coll_demo;

CREATE TABLE coll_demo (
  id      integer PRIMARY KEY,
  content varchar(50)
);

INSERT INTO coll_demo (id, content) VALUES
  (1, 'Banana'), (2, 'apple'), (3, 'Cherry'), (4, 'apricot');

CREATE INDEX idx_coll_demo_content ON coll_demo (content);
ANALYZE coll_demo;

DROP TABLE
CREATE TABLE
INSERT 0 4
CREATE INDEX
ANALYZE

在 en_US collation 下排序是「大小写不敏感优先」的：

SHOW lc_collate;

SELECT id, content FROM coll_demo ORDER BY content;

 lc_collate
------------
 en_US.UTF-8
(1 row)

 id | content
----+---------
  2 | apple
  4 | apricot
  1 | Banana
  3 | Cherry
(4 rows)

在 C collation 下排序变成纯字节序：

SELECT id, content FROM coll_demo ORDER BY content COLLATE "C";

 id | content
----+---------
  1 | Banana
  3 | Cherry
  2 | apple
  4 | apricot
(4 rows)

普通升序索引不被使用（因为 collation 不匹配）：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM coll_demo ORDER BY content COLLATE "C" LIMIT 2;

                                QUERY PLAN
--------------------------------------------------------------------------
 Limit (actual rows=2 loops=1)
   ->  Sort (actual rows=2 loops=1)
         Sort Key: content COLLATE "C"
         Sort Method: quicksort  Memory: 25kB
         ->  Seq Scan on coll_demo
(4 rows)

需要为 C collation 单独建索引：

CREATE INDEX idx_coll_demo_content_c ON coll_demo (content COLLATE "C");
ANALYZE coll_demo;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM coll_demo ORDER BY content COLLATE "C" LIMIT 2;

                                             QUERY PLAN
------------------------------------------------------------------------------------------------
 Limit (actual rows=2 loops=1)
   ->  Index Only Scan using idx_coll_demo_content_c on coll_demo (actual rows=2 loops=1)
         Heap Fetches: 0
(3 rows)

一个索引列只能有一种 collation

content 上的两个索引（默认 collation 与 C）是两个独立的索引。如果业务需要多种排序顺序，就得建多个索引——这是文本列特有的成本。

用 EXPLAIN 判断 Sort 是否被消除

判断标准非常直接：计划里有没有 Sort 节点。

有 Sort：                      Sort 被消除了：
┌─────────────────────┐        ┌──────────────────────────────┐
│ Limit               │        │ Limit                        │
│   -> Sort           │        │   -> Index Scan              │
│        Sort Key: ...│        │        Index Cond: ...       │
│        -> Index Scan│        │                              │
└─────────────────────┘        └──────────────────────────────┘

常用诊断手段：

-- 关闭索引扫描，对照看 Sort 的成本
SET enable_indexscan = off;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id FROM sales ORDER BY sold_at DESC LIMIT 20;
RESET enable_indexscan;

SET
                                          QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Sort (actual rows=20 loops=1)
         Sort Key: sold_at DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on sales (actual rows=200000 loops=1)
(5 rows)
RESET

对比两者耗时（EXPLAIN ANALYZE 的 actual time）就能量化排序的代价。

也可以直接检查排序操作是否使用了索引提供的有序输入：

SELECT id, amount FROM sales ORDER BY sold_at DESC LIMIT 5;
EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, amount FROM sales ORDER BY sold_at DESC LIMIT 5;

                             QUERY PLAN
---------------------------------------------------------------------
 Limit
   Output: id, amount, sold_at
   ->  Index Only Scan Backward using idx_sales_sold_at on sales
         Output: id, amount, sold_at

VERBOSE 下如果出现 Sort 节点，会明确列出 Sort Key 与 Output。

排序索引与前缀列的选择

一个索引只有一种排序顺序，因此要选「最常按什么排」：

需求	索引定义
最新优先（最常见）	(created_at DESC)
排行榜（分数高者先，同分早者先）	(score DESC, created_at ASC)
分组内最新	(group_id, created_at DESC)
待处理优先（NULL 先）	(assignee NULLS FIRST, created_at DESC)
时间正序（审计日志导出）	(created_at)

如果同时需要「最新优先」和「最早优先」，一个默认升序索引就够了——正扫给正序、反扫给逆序（NULL 位置随之翻转，需要确认业务是否接受）。

实战示例

场景：一个内容平台的首页信息流，需求是「某作者的最新 20 篇文章」和「全站热门榜」两种排序查询，全部要求无 Sort。

第 1 步：建表并灌入 500 万行数据

DROP TABLE IF EXISTS articles CASCADE;

CREATE TABLE articles (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id   integer     NOT NULL,
  title       text        NOT NULL,
  view_count  bigint      NOT NULL DEFAULT 0,
  published_at timestamptz,
  created_at  timestamptz NOT NULL
);

INSERT INTO articles (author_id, title, view_count, published_at, created_at)
SELECT
  (random()*10000)::int,
  'Article ' || g,
  (random()*100000)::bigint,
  CASE WHEN g % 10 = 0 THEN NULL ELSE now() - (g || ' minutes')::interval END,
  now() - (g || ' minutes')::interval
FROM generate_series(1, 5000000) AS g;

ANALYZE articles;

DROP TABLE
CREATE TABLE
INSERT 0 5000000
ANALYZE

第 2 步：基线——无索引时的「作者最新文章」

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, title, created_at FROM articles
WHERE author_id = 42
ORDER BY created_at DESC
LIMIT 20;

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   Buffers: shared hit=70617
   ->  Sort (actual rows=20 loops=1)
         Sort Key: created_at DESC
         Sort Method: top-N heapsort  Memory: 27kB
         ->  Seq Scan on articles (actual rows=504 loops=1)
               Filter: (author_id = 42)
               Rows Removed by Filter: 4999496
(8 rows)

缓冲访问 70617 次，且要排序。

第 3 步：建立 (author_id, created_at DESC) 索引

CREATE INDEX CONCURRENTLY idx_articles_author_created
  ON articles (author_id, created_at DESC);
ANALYZE articles;

CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, title, created_at FROM articles
WHERE author_id = 42
ORDER BY created_at DESC
LIMIT 20;

                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   Buffers: shared hit=8
   ->  Index Scan using idx_articles_author_created on articles (actual rows=20 loops=1)
         Index Cond: (author_id = 42)
         Buffers: shared hit=7
(5 rows)

Sort 消失，缓冲从 70617 降到 8。

第 4 步：热门榜（分数降序 + 时间升序，混合方向）

需求：按浏览量降序，浏览量相同时按发布时间从早到晚。

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, title, view_count FROM articles
ORDER BY view_count DESC, created_at ASC
LIMIT 20;

                                     QUERY PLAN
--------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   Buffers: shared hit=70617
   ->  Sort (actual rows=20 loops=1)
         Sort Key: view_count DESC, created_at
         Sort Method: top-N heapsort  Memory: 28kB
         ->  Seq Scan on articles (actual rows=5000000 loops=1)
(6 rows)

建立混合方向索引：

CREATE INDEX CONCURRENTLY idx_articles_hot
  ON articles (view_count DESC, created_at ASC);
ANALYZE articles;

CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, title, view_count FROM articles
ORDER BY view_count DESC, created_at ASC
LIMIT 20;

                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   Buffers: shared hit=5
   ->  Index Scan using idx_articles_hot on articles (actual rows=20 loops=1)
         Buffers: shared hit=4
(4 rows)

第 5 步：处理 published_at 的 NULL（未发布内容）

假设运营希望「未发布的排在最后」：

CREATE INDEX CONCURRENTLY idx_articles_published
  ON articles (published_at DESC NULLS LAST);
ANALYZE articles;

CREATE INDEX
ANALYZE

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, published_at FROM articles
ORDER BY published_at DESC NULLS LAST
LIMIT 20;

                                         QUERY PLAN
---------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan using idx_articles_published on articles (actual rows=20 loops=1)
         Heap Fetches: 0
(3 rows)

若省略 NULLS LAST，因为 DESC 默认 NULLS FIRST，语义会变成「未发布的排最前」：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, published_at FROM articles
ORDER BY published_at DESC NULLS FIRST
LIMIT 20;

                                              QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Scan Backward using idx_articles_published on articles (actual rows=20 loops=1)
(3 rows)

注意这里是 Index Scan Backward——反向扫描把 (published_at DESC NULLS LAST) 翻成了 (published_at ASC NULLS FIRST) 的逆序。

第 6 步：键集分页替代 OFFSET

深翻页对比：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, created_at FROM articles
ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;

                                           QUERY PLAN
----------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan Backward using idx_articles_author_created on articles (actual rows=1000020 loops=1)
         Heap Fetches: 0
(3 rows)

偏移 100 万需要读取 100 万零 20 行。键集分页：

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
SELECT id, created_at FROM articles
WHERE created_at < now() - interval '1000000 minutes'
ORDER BY created_at DESC LIMIT 20;

                                        QUERY PLAN
--------------------------------------------------------------------------------------------
 Limit (actual rows=20 loops=1)
   ->  Index Only Scan using idx_articles_published on articles (actual rows=20 loops=1)
         Index Cond: (published_at < ...)

无论翻多深，永远只读 20 行。

第 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 = 'articles'
ORDER BY pg_relation_size(indexrelid) DESC;

            index_name            | idx_scan |  size
----------------------------------+----------+---------
 articles_pkey                    |        0 | 108 MB
 idx_articles_hot                 |        1 | 145 MB
 idx_articles_author_created      |        1 | 146 MB
 idx_articles_published           |        2 | 139 MB
(4 rows)

第 8 步：清理

DROP TABLE articles;
DROP TABLE tickets;
DROP TABLE tasks;
DROP TABLE sales;
DROP TABLE coll_demo;
DROP TABLE null_demo;
DROP TABLE demo_sort;

注意事项

• ORDER BY col DESC 不等价于索引 (col)——DESC 的默认 NULL 位置是 FIRST，而索引 (col) 反向扫描给出的正是 DESC NULLS FIRST，这一点恰好匹配。但 ORDER BY col DESC NULLS LAST 就匹配不上了。

  -- 索引 (col) 反向扫描恰好满足它
  ORDER BY col DESC            -- = DESC NULLS FIRST
  -- 不匹配，会 Sort
  ORDER BY col DESC NULLS LAST

• 混合方向无法用单一方向的索引满足，必须把方向写进索引定义。

  -- 错误！(a, b) 无法满足 (a ASC, b DESC)
  CREATE INDEX idx_bad ON t (a, b);
  -- 正确
  CREATE INDEX idx_good ON t (a ASC, b DESC);

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

  -- 计划里出现 Bitmap Heap Scan 时，别指望免排序

• 排序列之前存在范围条件时，排序不成立。

  -- 索引 (a, b)，下面这句必须 Sort
  SELECT * FROM t WHERE a > 1 ORDER BY b;

• OFFSET 深翻页不会因为索引而变快，它必须跳过前面所有行。

  -- 不推荐（深翻页）
  SELECT * FROM t ORDER BY created_at DESC LIMIT 20 OFFSET 1000000;
  -- 推荐（键集分页）
  SELECT * FROM t WHERE created_at < :last ORDER BY created_at DESC LIMIT 20;

• 文本列排序依赖 collation，查询的 collation 与索引不一致时索引排序不被使用。

  -- 索引用默认 collation，查询用 C，不匹配
  SELECT * FROM coll_demo ORDER BY content COLLATE "C";

• 一个索引列只能承载一种 collation，多 collation 需求要建多个索引。

• DESC 索引不能被反向扫描当成 ASC 的替代（虽然数学上可行，但 NULL 位置会反转，要确认业务语义）。

• ORDER BY 里的表达式必须与索引表达式一致，否则不匹配。

  CREATE INDEX idx_x ON users ((lower(email)));
  -- 命中：ORDER BY lower(email)
  -- 不命中：ORDER BY email

• 仅索引扫描需要 visibility map 支持，表刚大量更新后 Heap Fetches 会很高，排序查询可能退化成普通索引扫描。

  VACUUM articles;   -- 更新 visibility map

• NULLS FIRST 索引的默认行为容易搞反：ASC NULLS FIRST 与 DESC NULLS LAST 是完全不同的两种顺序。

最佳实践

• 「最新优先」查询一律建 (排序列 DESC) 索引，这是收益最直接的优化：

  CREATE INDEX idx_articles_created_desc ON articles (created_at DESC);

• ORDER BY 的目标查询优先做成「索引前缀等值 + 排序列」：

  CREATE INDEX idx_articles_author_created
    ON articles (author_id, created_at DESC);

• 混合方向排序显式写进索引定义，不要指望优化器的方向推导：

  CREATE INDEX idx_hot ON articles (view_count DESC, created_at ASC);

• NULL 位置按业务语义显式声明，不要依赖默认值：

  CREATE INDEX idx_pub ON articles (published_at DESC NULLS LAST);

• 分页用键集分页替代 OFFSET，让深翻页的成本恒定：

  SELECT id FROM articles
  WHERE created_at < :last_seen
  ORDER BY created_at DESC LIMIT 20;

• 只读排序列时用仅索引扫描，并在建索引后 VACUUM：

  CREATE INDEX idx_sales_sold_at ON sales (sold_at);
  VACUUM sales;

• 用 EXPLAIN (ANALYZE, BUFFERS) 确认 Sort 真的消失，不要只看 EXPLAIN 的估算：

  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS)
  SELECT id FROM sales ORDER BY sold_at DESC LIMIT 10;

• 必要时用 enable_indexscan / enable_sort 做对照实验，量化排序代价（记得 RESET）：

  SET enable_indexscan = off;
  EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF, BUFFERS) SELECT ... ;
  RESET enable_indexscan;

• 文本排序统一 collation，避免为每种 collation 建索引：

  CREATE INDEX idx_content_c ON coll_demo (content COLLATE "C");

• 定期复核排序索引的使用率，idx_scan 长期为 0 的排序索引应考虑删除：

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

• 大表建排序索引用 CONCURRENTLY，避免阻塞写入：

  CREATE INDEX CONCURRENTLY idx_articles_hot
    ON articles (view_count DESC, created_at ASC);

下一节

继续学习：哈希索引原理

参考链接

• PostgreSQL 官方文档：索引与 ORDER BY
• PostgreSQL 官方文档：索引与排序规则
• PostgreSQL 官方文档：CREATE INDEX
• PostgreSQL 官方文档：多列索引
• PostgreSQL 官方文档：仅索引扫描
• PostgreSQL 官方文档：B-Tree 索引实现
• PostgreSQL 官方文档：使用 EXPLAIN