查询没变，计划却变了
一句话总结
如果查询和索引都没有变化，性能却在某一天突然下降，那么改变的就是优化器掌握的数字。而如果表一直没有缩小，不是因为没有可删除的内容，而是因为数据库尚不能确定是否可以删除。
为什么需要理解这一点
某个聚合 API 昨天还只需 40 毫秒，今天却要 6 秒。期间没有部署，索引也没有变化。数据量确实增加了，但只是大约三倍，并非一百倍。
这时最常见的应对方式是再建一个索引，但通常这并不是答案。打开执行计划，原因往往就写在其中一行里。
它如何运作
加上 EXPLAIN (ANALYZE, BUFFERS) 后，每个计划节点都会显示两组数字。前一组括号里的 rows= 是估算值，后一组括号里的 rows= 是实际值。下面就是在实验环境中直接得到的输出。
Aggregate (cost=1988.00..1988.01 rows=1 width=8) (actual time=8.282..8.282 rows=1 loops=1)
Buffers: shared hit=988
-> Seq Scan on events (cost=0.00..1988.00 rows=1 width=0) (actual time=0.925..5.801 rows=60000 loops=1)
Filter: (tenant_id = 41)
Rows Removed by Filter: 20000
估算为 1 行，实际却是 60000 行。这一行就足以解释当天的整个故障。优化器认为这个条件只会返回一行，因此选择了让这一行与另一张表的所有行逐一匹配的嵌套循环。实际上返回了 6 万行，于是该循环执行了 6 万次。
为什么会估成 1？因为批量导入新租户的数据后没有执行 ANALYZE。pg_stats 中仍保留着导入前的数据分布，其中不存在 tenant_id = 41。查询一个统计信息中不存在的值时，优化器就会回答“几乎没有”。
以下数据也来自同一环境。
통계가 낡은 상태 Nested Loop 실행 3059 ms
analyze events;
통계를 고친 뒤 Hash Join 실행 66 ms
**改变的既不是查询，也不是索引，而只是优化器掌握的数字。**前一个数字在多次测量中会在 1.5 秒到 6 秒之间波动，因此应该关注比例，而非绝对值。
阅读执行计划时还有一个陷阱。如果计划中的 loops= 不等于 1，显示的 rows 和时间都是每次执行的平均值。并行计划中，每个工作进程都会执行一次，所以 rows=100000 loops=2 实际表示处理了 20 万行。这个位置很容易产生误解，因此需要准确读取数字时，最好先通过 set max_parallel_workers_per_gather = 0 关闭并行，再生成一次执行计划。
增加索引会更好吗
针对同一故障，我们创建 events(tenant_id) 索引后重新进行了测量。以下结果来自包含 20 万行的数据集。
인덱스 없음 · 통계 낡음 9774 ms Nested Loop / Seq Scan 예상 1행
인덱스 있음 · 통계 낡음 205 ms Nested Loop / Index Scan 예상 1행
인덱스 없음 · 통계 정상 60 ms Hash Join / Seq Scan 예상 234564행
索引确实有帮助，耗时从 9.7 秒降到了 0.2 秒。但**执行计划仍然是错的。**估算值依旧是 1 行，优化器也仍在选择嵌套循环。修正统计信息的一组即使没有索引，速度也快了三倍以上。
这正是不能先动索引的原因。索引缓解了症状，却没有消除根因，而且每次写入都要为索引付出成本。下次同一张表再次批量导入数据时，相同的问题还会重演。
此外，说索引总会带来损失也不正确。保留索引并修正统计信息后，优化器决定不使用索引，耗时为 46 毫秒；将 enable_seqscan = off 设为关闭以强制使用索引后，反而只用了 33 毫秒。这是因为整张表都位于 shared_buffers 中，而且目标行在物理上集中存放。**无论是“有索引就会更快”，还是“大结果集使用索引会吃亏”，在现场实际测量之前都无法断言。**正确的顺序是先让执行计划的估算与实际相符，再进行测量。
为什么更新后表反而变大
PostgreSQL 不会就地修改一行。它会写入一个新版本，并在旧版本上留下“从该事务之后不可见”的标记。因此，UPDATE 实际上相当于插入，DELETE 也不会释放空间。遗留下来的旧版本就是死元组。
下面是仅修改一个列、对 6 万行执行 update 后的结果。
갱신 전 9,945,088 바이트
갱신 후 17,358,848 바이트 n_dead_tup = 60000
数据内容甚至没有多一个字符，表的大小却变成了原来的 1.7 倍。负责回收这些空间的工作是 VACUUM，平时通常由 autovacuum 自动完成。
地平线——vacuum 无法清理的原因
然而，即使手动执行 VACUUM，有时表仍不会缩小。vacuum 会自行说明原因。
tuples: 0 removed, 131456 remain, 60000 are dead but not yet removable
removable cutoff: 970, which was 10 XIDs old when operation ended
这不是**“没有可以删除的内容”而是“目前还不能删除”。因为仍处于打开状态的事务可能还需要看到那些旧版本。该边界就是 removable cutoff，而它由仍存活的最老事务**决定。
查找是谁占住这条边界时，人们经常只检查 backend_xmin，但这样可能找不到真正的对象。
pid | application_name | state | backend_xid | backend_xmin
------+------------------+---------------------+-------------+--------------
941 | nightly-batch | idle in transaction | 932 |
947 | api-order | active | 933 | 932
949 | api-cart | active | 934 | 932
1050 | psql | active | | 932
真正占住 cutoff 的是 941，但 941 的 backend_xmin **是空的。**它是通过自己的事务 ID，也就是 backend_xid 932，占住地平线的。相反，在 backend_xmin 中带有 932 的 947、949 和 1050，只是在各自的快照中记录了 941 仍然存活这一事实。即使终止这些会话，地平线也不会移动。
查找方法只有一个——找到最老的 backend_xid。
select pid, application_name, backend_xid, now() - xact_start as age
from pg_stat_activity
where backend_xid is not null
order by age(backend_xid) desc limit 1;
而 941 正是前面的锁等待链中已经见过的那个会话。**症状有两个，根因却只有一个。**一张表无法缩小的原因可能根本不在这张表里——这正是本课程要传达的核心。
生产现场中的表现
**第一，批量导入脚本的最后一行应该是 ANALYZE。**autoanalyze 最终会运行，但没有人知道它会在什么时候运行，而等待的那几分钟就是故障持续时间。由执行导入的人当场补上这一行，成本最低。
第二，要记住根因可能在表外。收到“只有这张表变得特别大”的报告时，人们往往先排查该表的索引或导入模式；但在这个故障中，表本身没有任何问题。如果 n_dead_tup 很大，而且 VACUUM 返回 not yet removable，从那一刻起要查看的就不再是表，而是事务列表。
**第三，n_dead_tup 是统计值，不是实测值。**它由统计信息收集器更新，因此可能与实际情况不一致。想获得确定结论时，最好阅读 VACUUM (VERBOSE) 的输出。该输出还会同时给出 cutoff，让你一次就能知道“为什么无法清理”。
下一项实验要做什么
你将接手一个同时出现锁等待链、失真的执行计划和膨胀表这三种症状的数据库，分别用数字提取每种症状的证据，最后汇总成一份诊断报告。评分程序会从仍在运行的数据库中重新提取你填写的数字并进行核对，因此可以自由选择查找方法，但诊断必须正确。