《AI Agent 工程实战》5.3 查询优化：EXPLAIN 定位慢查询并加索引

5.3 查询优化：EXPLAIN 定位慢查询并加索引

前两节把表建好、把并发写对，但有一件事一直没验证：这些查询在数据变多之后还快吗。5.1 里插四条消息、5.2 里抢一个 run，都是「十行以内」的演示，随便怎么查都是毫秒级。可真实的 messages 表会涨到千万行，runs 表里堆着几个月的历史。索引不是「表建好就顺手加上」，而是「先看计划、再决定加什么」——这一节就用 EXPLAIN 把这件事走一遍。

本节把 ResearchCopilot 推进到查询性能：造 5 万条真实规模的消息，对「按 workspace + session 拉最近 N 条」这条最热的查询跑 EXPLAIN (ANALYZE, BUFFERS)，看它从 Seq Scan 变成 Index Scan 的真实数字，并定出一份索引清单。

5.3.1 造一批真实规模的数据

先用 generate_series 灌 5 万条消息。为了贴近真实——会话长度分布是长尾的，少数长会话积累了大量消息——我们让一个「热会话」独占 8000 条，其余 42000 条摊到另外 499 个会话：

import psycopg
DSN = "postgresql://postgres:postgres@localhost:15435/research"
with psycopg.connect(DSN, autocommit=True) as c:
    c.execute("TRUNCATE messages, runs, sessions RESTART IDENTITY CASCADE")
    c.execute("""INSERT INTO sessions (workspace_id, title)
                 SELECT 'ws-' || (g % 20), 'session ' || g FROM generate_series(1, 500) g""")
    c.execute("""WITH s AS (SELECT id, workspace_id FROM sessions ORDER BY id LIMIT 1)
                 INSERT INTO messages (workspace_id, session_id, role, payload, created_at)
                 SELECT s.workspace_id, s.id, 'user',
                        jsonb_build_object('role','user','content','msg ' || m),
                        now() - (m || ' seconds')::interval
                 FROM generate_series(1, 8000) m, s""")
    c.execute("""WITH s AS (SELECT id, workspace_id, row_number() OVER (ORDER BY id) AS rn FROM sessions)
                 INSERT INTO messages (workspace_id, session_id, role, payload, created_at)
                 SELECT s.workspace_id, s.id, 'user',
                        jsonb_build_object('role','user','content','msg ' || m),
                        now() - (m || ' seconds')::interval
                 FROM generate_series(1, 42000) m JOIN s ON s.rn = ((m - 1) % 499) + 2""")
    c.execute("ANALYZE messages")

真跑输出：

total: 50000
hot session: (UUID('00180fe2-026d-45bb-89d7-e54522ecc6e8'), 'ws-1', 8000)

5 万条消息、500 个会话，其中一个热会话有 8000 条。灌完必须跑一次 ANALYZE——优化器靠统计信息估行数，没有统计信息，它给的计划全是瞎猜。这也是很多人「本地快、线上慢」的原因之一：本地表小，优化器怎么选都对。

为什么非要造「一个热会话占 8000 条」这种偏斜分布，而不是均匀撒 500 份？因为真实的会话长度是长尾的：绝大多数会话几轮到几十轮就结束，但总有几个「深度研究」会话会积累上千条消息——用户让 Agent 反复检索、追问、修正，消息只增不减。用均匀分布测出来的计划，会低估「单会话内取最近 N 条」这条查询的真实压力。造测试数据时，分布的形状比总量更重要。

5.3.2 先看慢查询长什么样

要优化的查询就是 5.1 里那条：给定 workspace 与 session，取最近 20 条消息（按时间倒序）。把 EXPLAIN (ANALYZE, BUFFERS) 加在前面——ANALYZE 会真跑这条查询并给出实际耗时，BUFFERS 会告诉你它读了多少个数据页：

Q = ("SELECT id, role, payload->>'content' AS content FROM messages "
     "WHERE workspace_id = %s AND session_id = %s ORDER BY created_at DESC LIMIT 20")
for row in conn.execute("EXPLAIN (ANALYZE, BUFFERS) " + Q, (ws, sid)):
    print(row[0])

真跑输出（PostgreSQL 17.11）：

Limit  (cost=1511.55..1511.60 rows=20 width=53) (actual time=8.756..8.759 rows=20 loops=1)
  Buffers: shared hit=718
  ->  Sort  (cost=1511.55..1515.54 rows=1599 width=53) (actual time=8.754..8.755 rows=20 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 26kB
        Buffers: shared hit=718
        ->  Seq Scan on messages  (cost=0.00..1469.00 rows=1599 width=53) (actual time=0.021..6.636 rows=8000 loops=1)
              Filter: ((workspace_id = 'ws-1'::text) AND (session_id = '00180fe2-026d-45bb-89d7-e54522ecc6e8'::uuid))
              Rows Removed by Filter: 42000
              Buffers: shared hit=715
Planning Time: 0.313 ms
Execution Time: 8.804 ms

从下往上读这份计划，三个数字说明了一切：

• Seq Scan on messages：数据库选择顺序扫描整张表。它没有可用的索引，只能一行行看。
• Rows Removed by Filter: 42000：扫了 5 万行，其中 42000 行在 Filter 处被丢掉——为了 8000 行数据，白读了 42000 行。数据越多，这个浪费越大。
• Buffers: shared hit=718：读了 718 个 8KB 的数据页（约 5.6 MB）。hit 表示命中缓存，如果换成 read（要从磁盘读），耗时会立刻翻几倍。

外层还有一次 Sort（top-N heapsort）：因为要按 created_at DESC 排序，而顺序扫描出来的数据是无序的，所以得先取出 8000 行、再排序、再取前 20。整条查询 8.804 ms——在 5 万行的表上已经能感觉到，放到千万行就是秒级。

读计划时，每个节点括号里有两组数：cost=... 是优化器的估算（基于统计信息，单位是抽象的「代价」，不是毫秒），actual time=... 是 ANALYZE 真跑出来的实测（毫秒）。两者差距大，往往说明统计信息不准，该重新 ANALYZE。计划是从下往上、从里往外执行的：最内层的 Seq Scan 先产出 8000 行，交给 Sort 排序，最外层的 Limit 截取 20 行。rows=20 在最外层、rows=8000 在最内层，正好印证了「为了 20 行读了 8000 行」这件事。

5.3.3 加一条对的索引

慢的根因是「过滤条件没有索引可用」。查询的过滤是 workspace_id = ? AND session_id = ?，排序是 created_at DESC——那就建一条正好覆盖这三者的复合索引，且把排序列也写进去：

CREATE INDEX idx_messages_ws_session_created
    ON messages (workspace_id, session_id, created_at DESC);

再跑一次同一条 EXPLAIN (ANALYZE, BUFFERS)：

Limit  (cost=0.41..34.49 rows=20 width=53) (actual time=0.048..0.052 rows=20 loops=1)
  Buffers: shared hit=1 read=3
  ->  Index Scan using idx_messages_ws_session_created on messages  (cost=0.41..2677.19 rows=1571 width=53) (actual time=0.047..0.050 rows=20 loops=1)
        Index Cond: ((workspace_id = 'ws-1'::text) AND (session_id = '00180fe2-026d-45bb-89d7-e54522ecc6e8'::uuid))
        Buffers: shared hit=1 read=3
Planning Time: 0.117 ms
Execution Time: 0.066 ms

计划彻底变了：Seq Scan 变成 Index Scan，Filter 变成了 Index Cond（条件在索引里直接命中，不再逐行过滤），连那个 Sort 节点也消失了——因为索引本身按 created_at DESC 排好序，数据库顺着索引读 20 条就够了。对比：

读取页数从 718 降到 4，耗时降到约 1/130。注意 Index Cond 里的两列顺序和索引定义完全一致：(workspace_id, session_id) 是索引的前两列，所以数据库能同时用上它们；如果把索引建成 (session_id, workspace_id, ...) 也能命中，但 workspace_id 打头更贴合「所有查询都先按租户过滤」的访问模式。这条索引占 2456 kB，约为 messages 表（5720 kB）的 43%——这是为速度付的价，5.3.5 会算这笔账。

这里还有一个细节值得品味：Buffers: shared hit=1 read=3 里的 read 表示这 3 个页不在缓存里、要从磁盘读。加了索引之后读取的页数虽然少了，但首次访问仍需一次磁盘 IO；而 hit=718 是「全在缓存里」——也就是说，即便最坏情况下 718 个页全在内存，它仍然比 4 个页慢一百多倍。页数（工作量）才是根本，缓存命中率只是缓解。这也是为什么「本地测试快」常骗人：本地数据全在 page cache 里，掩盖了全表扫描的真实代价。

5.3.4 索引清单：该加什么

把 ResearchCopilot 四个表的索引一次列全，每条都对应一个真实访问模式：

第二条 messages(session_id) 容易被忽略：删一个 sessions 行时，ON DELETE CASCADE 要找出所有引用它的 messages 并删除。如果 session_id 上没有索引，这个级联查找就是一次全表扫描。实测删一个含 8000 条消息的会话，外键触发器的耗时从 4.58 ms（无索引）降到 2.75 ms（有索引）——差别不算夸张，但删会话的频率和表的大小都会增长，全表扫描的代价是线性的。

第三条是部分索引（partial index），它只索引满足 WHERE 的行，是队列场景的利器。worker 轮询的查询是「找一条 queued 的 run」，而 runs 表里绝大多数行早就跑完了——只给 queued 建索引，体积可以小到忽略不计：

CREATE INDEX idx_runs_queued ON runs (workspace_id, created_at) WHERE status = 'queued';

真跑（runs 表 2 万行、其中 100 行是 queued）：

--- 无索引：worker 轮询 ---
Limit  (cost=547.02..547.03 rows=1 width=24) (actual time=1.420..1.421 rows=1 loops=1)
  ->  Sort  (cost=547.02..547.04 rows=5 width=24) (actual time=1.419..1.420 rows=1 loops=1)
        ->  Seq Scan on runs  (cost=0.00..547.00 rows=5 width=24) (actual time=0.032..1.392 rows=40 loops=1)
              Filter: ((workspace_id = 'ws-4'::text) AND (status = 'queued'::text))
              Rows Removed by Filter: 19960
Execution Time: 1.439 ms
--- 部分索引 WHERE status='queued' ---
Limit  (cost=0.14..4.96 rows=1 width=24) (actual time=0.021..0.021 rows=1 loops=1)
  ->  Index Scan using idx_runs_queued on runs  (cost=0.14..24.21 rows=1 width=24) (actual time=0.020..0.020 rows=1 loops=1)
        Index Cond: (workspace_id = 'ws-4'::text)
Execution Time: 0.033 ms
部分索引大小: 16 kB  表大小: 1976 kB

Seq Scan（1.439 ms、丢弃 19960 行）变成 Index Scan（0.033 ms），而索引只有 16 kB——不到整表的 1%。如果建的是全量索引，就得索引全部 2 万行，体积和写放大都要大得多。部分索引的精髓是：你只查 queued，就只索引 queued。

复合索引的列顺序也值得单独说。(workspace_id, session_id, created_at DESC) 这个顺序不是随便排的：索引只能从最左列开始用，所以 workspace_id 必须打头（每条查询都带它），session_id 次之（第二常用的过滤），created_at 放最后（用于排序与范围）。反过来写成 (created_at, workspace_id, session_id) 就废了——按时间排序虽然能命中，但 workspace_id 和 session_id 的过滤退化成逐行检查。排索引列的顺序，等于排查询里过滤条件的优先级。

sessions 与 memories 的索引则是另一类模式：sessions (workspace_id, updated_at DESC) 服务于「列出这个 workspace 最近的会话」，把排序键放进索引能免掉一次排序；memories (workspace_id, user_id, created_at DESC) 服务于「取某个用户的最近记忆」。它们和 messages 那条是同一个套路——过滤列在前、排序列在后。真正复杂的是 memories.embedding 的向量检索：real[] 上的等值比较毫无意义，要按余弦距离找最近邻，得换成 pgvector 的 vector 类型并建 HNSW 索引，那是 /posts/postgresql/ 专题的内容，本节不展开。

5.3.5 哪些索引不该加

索引不是免费的。每加一条，写入时都要多维护一棵 B-tree；它占磁盘、占内存、还会让优化器在选择时多花时间。下面三类索引，在 ResearchCopilot 里不该加。

一、与复合索引重复或派不上用场的单列索引。 很多人会「顺手」给 messages.created_at 建一条单列索引，觉得「排序要用」。实测把 (created_at DESC) 单独建出来，上面那条会话查询根本不会用它——数据库仍然选 idx_messages_ws_session_created，因为单列索引无法同时满足 workspace_id 和 session_id 的过滤。排序键只有和过滤键合在一条索引里才有意义，单拎出来就是白占空间。

二、低基数列上的索引。 messages.role 只有四个取值，任何一次查询都会命中很大比例的行。本例数据里全表都是 user，实测优化器在 Seq Scan（5.83 ms）和 index-only scan 之间反复摇摆——因为「100% 的行都匹配」等于索引没有过滤能力，优化器找不到用它的理由。列的选择性太低时，索引要么用不上、要么帮倒忙。

三、为「将来可能查」而预建的索引。 写放大是实打实的，用真实数据量一下：往 messages 插入 2 万行，不加业务索引约 32–35 ms，加上复合索引后涨到 58–59 ms——接近两倍。这个代价在小批量下不显眼，但在高写入的 Agent 服务里会累积。更别说复合索引本身就占了表 43% 的磁盘。索引要由查询驱动，不是由「以防万一」驱动——先跑 EXPLAIN，看到 Seq Scan 且数据量够大，再建。

一个更隐蔽的陷阱：payload 是 jsonb，如果你给整个 payload 建 GIN 索引，写入成本会非常高（每插一条消息都要更新倒排索引），而 ResearchCopilot 并不按消息内容做全文检索——真正要检索的是记忆，那是另一张表的事。给错的列建索引，比不建索引更糟。

把这三条收成一句可执行的原则：每一个索引都要能指着一条具体的查询说「我为它而生」，否则就别建。建完再回头用 EXPLAIN 验证它真的被选中、用 pg_stat_user_indexes 验证它真的在被用——索引不是一次性的设计动作，而是一个「建、测、删」的循环。

5.3.6 怎么确认索引真的被用上

建了索引不等于用上了。EXPLAIN 能看「这一条查询走不走它」，但要回答「过去一段时间它到底被用了多少次」，得查 pg_stat_user_indexes。连续跑 100 次会话查询后：

indexrelname                    | idx_scan |    size
idx_messages_ws_session_created |      152 | 2456 kB
messages_pkey                   |        0 | 1112 kB

idx_messages_ws_session_created 的 idx_scan 是 152（100 次显式查询加上之前演示累积的次数），证明它确实在扛热路径。更值得注意的是 messages_pkey 的 idx_scan = 0——主键索引在这段时间里一次都没被用来做查询。这不代表它没用（主键的唯一性约束、外键引用都靠它），但它提醒我们：idx_scan=0 的索引是「疑似冗余」的强信号，值得逐个审视是否真有必要，尤其当你发现两条索引的前缀重复时。

顺带把 PostgreSQL 常用的几种索引类型理一张速查表，避免「什么场景都上 B-tree」：

最后一行值得展开：如果查询只 SELECT 已经在索引里的列，PostgreSQL 能做 index-only scan，连堆表都不用回访，再快一截。本节那条查询要取 payload->>'content'，它不在索引里，所以必须回表（Index Scan）；如果只需要 id 和 created_at，把这两列做成覆盖索引就能再省一次磁盘访问。要不要为覆盖再塞列进索引，是一场「省一次回表」对「索引变大、写放大增加」的权衡——又是回到 5.3.5 那条原则：由查询驱动。

5.3.7 真实环境与验收清单

本节所有计划与耗时都来自本机真连的 PostgreSQL 17.11（容器 ab-pg，postgresql://postgres:postgres@localhost:15435/research），数据规模 5 万条消息 / 2 万条 run。真实环境的补充：

• EXPLAIN (ANALYZE) 会真执行语句。对 SELECT 无所谓，但对 UPDATE / DELETE 要放进事务里跑完再 ROLLBACK，否则会真改数据。本节演示外键级联删除时就是这么做的。
• 小表上的计划不可信。本地 5 万行时 Seq Scan 也许只要几毫秒，优化器甚至可能主动选它；到了千万行，同一个查询就是秒级。判断一条索引该不该建，要在接近生产的数据量上跑计划。
• 生产优先用 pg_stat_statements 找慢查询，而不是靠猜。EXPLAIN 是「诊断某一条」，pg_stat_statements 是「找出该诊断哪一条」——后者更适合线上。
• 索引要能被真正用上。WHERE 里对列做函数包装（如 WHERE lower(title) = ...）会让普通索引失效，除非索引也建成对应的表达式索引。这一节用的都是原样比较，没有这个坑。
• 加索引本身要小心锁。CREATE INDEX 默认会阻塞该表的写入，生产上大表必须用 CREATE INDEX CONCURRENTLY（不阻塞写入，但更慢、且失败会留下无效索引需要清理）。本节表小，普通 CREATE INDEX 秒完，但这条规则要记牢。

本节验收清单：

• 5 万条数据能灌出长尾分布（一个热会话 8000 条）
• 慢查询的 EXPLAIN 能看到 Seq Scan 与 Rows Removed by Filter
• 加复合索引后变成 Index Scan，Sort 节点消失，耗时降到 1/100 量级
• 部分索引只占全表 1% 的体积，却把轮询从 1.4 ms 降到 0.03 ms
• 说得清哪三类索引不该加，以及为什么
• 能用 pg_stat_user_indexes 找出 idx_scan=0 的疑似冗余索引
• 明白 Buffers 的页数比 Execution Time 更能反映工作量

数据层的三节到此收尾：Schema 定形状、事务保正确、索引提速度。但每次请求都去数据库查一遍检索结果和嵌入，成本仍然偏高——下一节 6.1 Redis 缓存：检索结果与嵌入缓存命中率 会在数据库前面加一层缓存，把重复的计算挡掉。

最后留一个提醒：索引优化的尽头往往是「少查几次」。把 8.8 ms 的查询压到 0.07 ms 很爽，但如果同一份检索结果在一个请求里被查了三次，省下的那点时间立刻被抵消。真正的性能杠杆有两根——一根是让每次查询更快（本节），另一根是让查询发生得更少（下一章）。两根一起用，才有数量级的提升。

先量，再改，改完再量——这是本节唯一想让你带走的方法论。EXPLAIN 是尺子，索引是手术刀，别反过来用。

阅读导航：上一节：5.2 事务与并发：run 状态机原子更新、行锁防重复执行 · 下一节：6.1 Redis 缓存：检索结果与嵌入缓存命中率 。