规划器

连接策略负责组合两路输入，并不是某段 SQL 永久绑定的属性。只查询三个客户时，通过索引反复定位购买记录可能很合算；查询所有购买记录时，扫描一遍并构建哈希表可能更合适。PostgreSQL 根据预计工作、可用访问路径、顺序和内存预期，在合法候选中选择。表有多大确实重要，但过滤后还剩多少行，比表的名字或它平时被称为“大表”更重要。 反复查找与复用查找表 嵌套循环每取一条外层记录，就利用这条记录执行一次内层路径。内层并不一定扫描整张表，也可以进行索引或位图查找。外层结果很少、内层定位足够精确时，重复执行的成本可能很低。读计划时，应把内层节点的每轮行数与 loops 放在一起看，才能知道实际重复了多少工作，而不是看到循环就直接判断它低效。 哈希连接从一路输入建立临时哈希表，再用另一边的记录探测匹配。这是执行期间的数据结构，与 USING hash 建立的持久索引没有直接关系；不需要先建一个哈希索引才能使用哈希连接。真正的构建侧是计划中 Hash 节点下方的子树。应查看这个位置，不能假设 SQL 里写在左边或右边的表必然负责构建，也不能只比较两张基础表的原始大小。 如果两路输入已经具备适当顺序，或者排序的成本值得支付，合并连接也是候选。因此，嵌套循环和哈希连接并没有穷尽规划器的全部选择。不同过滤条件、输出要求和合法连接条件，都可能改变哪些方案值得考虑。算法名称只是计划的一部分，不能代替对输入与结果规模的分析。 坏掉的样子 外层结果被低估时，原本看似便宜的单次内层查找会被重复过多次数。哈希连接也可能因为超出内存预算而分成多批，产生临时读写，并不保证始终完整留在内存中。应检查批次数和临时缓冲区活动，不能看见 Hash 就推导出“恒定的单次查找成本”或“所有规模下都线性且很快”。 大量重复连接键还可能产生巨大的结果集合，无论使用哪种查找策略都需要产生查询要求的那些行。意外的多对多连接，应通过修正关系或查询语义解决，而不是只把连接节点换个名字。如果两侧每个键都重复很多次，乘出来的输出规模本身就可能是主要瓶颈。 原生 PostgreSQL 不会解释 /*+ HashJoin */ 这样的优化器提示注释。扩展可以提供提示功能，但那属于另外的环境契约。规划器开关可以支持受控对比实验，不过不能因为一次小数据实验，就永久在全局关闭某种连接方式；其他查询的输入和收益可能完全不同。 快速验证 在 PostgreSQL 18 的 psql 中执行下面事务。它创建五百个客户和五万条购买记录，每个客户对应一百条购买记录，并在引用列上建立普通 B-tree 索引。两条查询计算同一种连接关系的金额总和，区别只在于是否限制客户范围。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE customers (id integer PRIMARY KEY); CREATE TEMP TABLE purchases (id integer PRIMARY KEY,customer_id integer,amount integer); INSERT INTO customers SELECT g FROM generate_series(1,500) AS g; INSERT INTO purchases SELECT g,1+(g-1)%500,10 FROM generate_series(1,50000) AS g; CREATE INDEX purchases_customer ON purchases(customer_id); ANALYZE customers; ANALYZE purchases; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id; DO $$ BEGIN IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id WHERE c.id<=3) IS DISTINCT FROM 3000::bigint THEN RAISE EXCEPTION 'wrong selective join'; END IF; IF (SELECT sum(p.amount) FROM customers c JOIN purchases p ON p.customer_id=c.id) IS DISTINCT FROM 500000::bigint THEN RAISE EXCEPTION 'wrong broad join'; END IF; END $$; ROLLBACK; PostgreSQL 18.6 实测中，限定客户编号不超过三时采用嵌套循环。内层位图堆扫描每轮返回一百条购买记录，一共执行三轮。连接输出三百行，金额总和为三千。嵌套循环里出现位图扫描是正常组合：扫描节点描述内层记录怎样被取到，连接节点描述怎样反复利用外层记录组合结果，两者不是互斥选项。 ...

部分索引可以把高频访问的少量行放在更小的结构中。假如 open 任务很少，而请求多数只找 open 任务，在 id 上建立 WHERE status = 'open' 的索引就值得评估。但它是否可用，取决于规划器能为当前查询证明什么，不只是应用在某次执行时碰巧传入了 open。准备语句的计划方式会直接影响这份证据。 证明不需要被排除的行 索引里没有 closed 任务，因此不能独立回答可能需要 closed 行的查询。PostgreSQL 必须在规划时识别查询条件蕴含索引谓词。显式写出的 status = 'open' 提供了这个事实；通用计划中的 status = $1 则没有，因为同一份计划还必须在参数为 closed 时保持正确。规划器不能依靠应用的口头约定丢弃可能需要的数据。 蕴含关系不总是逐字相同。PostgreSQL 能识别一些简单逻辑关系，但不是任意表达式的定理证明器。统一真实需要的谓词，有助于避免重构把原本可用的索引条件藏起来。仅过滤索引键 id 也不能证明状态为 open，即使当前数据或业务习惯让它经常如此。性能优化必须保持查询语义，不能把偶然数据状态当成永久约束。 准备计划改变可用信息 准备语句可以使用根据具体参数规划的 custom plan，也可以使用跨参数复用的 generic plan。状态参数为 open 时，定制计划可能知道所需谓词并考虑部分索引；通用计划面对未知状态，就不能把该索引当成完整答案路径。SQL 文本没有变化，改变的是规划阶段掌握的信息，这也是同一语句不同执行阶段出现不同计划的原因之一。 ...

EXPLAIN 描述 PostgreSQL 打算执行的计划，EXPLAIN ANALYZE 则真正执行它并补充测量结果。比较这两组数字之前，必须先理解每个数字究竟在数什么。表面上的估计偏差，既可能来自统计过时，也可能来自节点重复执行，或者父节点提前停止取行。行数吻合只能增强我们对模型某一部分的信任，不能证明这个计划已经是所有可能方案里最快的一个。 把节点放回执行上下文 估计的 rows 是节点输出行数，不一定是它实际检查的全部元组数。一个扫描可以检查几千条记录，过滤后只交出十条。启动成本与总成本使用规划器自己的单位，不是毫秒。父节点的成本已经包含子节点工作，因此把整棵树所有成本相加会重复计算。实际时间也通常包含子节点耗时，不能把它当成彼此独立的一组费用来求和。 对于重复执行的节点，实际行数和耗时按每次执行的平均值报告，执行次数由 loops 表示。一个实际输出两行、循环三次的节点，总共提供六行。过滤掉的行数同样按每轮平均值展示，四舍五入可能影响小数字。应当从底部扫描向上追踪，找到实际数据规模第一次明显偏离估计的位置，同时遵守这些执行边界，而不是挑出整棵树中最显眼的一对数字就下结论。 LIMIT、EXISTS 等上层操作还可能提前停止请求子节点。此时子节点的估计描述完整执行时可能产生的输出，实际值却只记录父节点真正取走的部分。这不自动意味着统计错误。排序也可能已经读完整个输入，却只向上交出所需的前几行；“输出少了”与“底层工作少了”不是一回事。 坏掉的样子 给写语句加上 EXPLAIN ANALYZE 会真的执行写入。外层事务回滚可以保护事务性的表变更，但不能撤销所有副作用，例如序列已经发出去的号码，或函数调用触发的外部行为。在执行测量前，应使用可丢弃的夹具，或者明确语句及其函数、触发器的完整副作用。不能因为输出长得像报告，就把它视为只读操作。 另一个常见误判是仅凭 BUFFERS 诊断磁盘。共享缓冲区命中表示没有发生 PostgreSQL 缓冲区未命中；报告中的读取仍可能由操作系统缓存满足，不能直接等同于设备读取次数，访问计数也不是不同数据块的数量。即使估计行数完全准确，仍可能存在锁等待、内存不足引发的临时文件、配置问题，或没有被选中的更合适访问路径。 快速验证 在 PostgreSQL 18 的 psql 中执行下面事务。它建立三个父记录、三十个子记录，分别观察重复的横向子查询和单独的前三名查询。数据全部放在临时表中，最后回滚清理，避免把演示对象留在业务表里。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE parents (id integer PRIMARY KEY); CREATE TEMP TABLE children (parent_id integer,value integer); INSERT INTO parents VALUES (1),(2),(3); INSERT INTO children SELECT 1+(g-1)%3,g FROM generate_series(1,30) AS g; ANALYZE parents; ANALYZE children; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT p.id,c.value FROM parents p CROSS JOIN LATERAL ( SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2 ) c; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT value FROM children ORDER BY value DESC LIMIT 3; SELECT p.id,c.value FROM parents p CROSS JOIN LATERAL ( SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2 ) c ORDER BY p.id,c.value DESC; DO $$ BEGIN IF (SELECT array_agg(value ORDER BY parent_id,value DESC) FROM ( SELECT p.id AS parent_id,c.value FROM parents p CROSS JOIN LATERAL ( SELECT value FROM children WHERE parent_id=p.id ORDER BY value DESC LIMIT 2 ) c ) s) IS DISTINCT FROM ARRAY[28,25,29,26,30,27] THEN RAISE EXCEPTION 'wrong lateral result'; END IF; END $$; ROLLBACK; PostgreSQL 18.6 实测的横向查询采用嵌套循环。内层 Limit 显示实际行数为二、loops=3，因此整个连接输出六行。其下的扫描每轮返回十个符合条件的孩子，并过滤掉二十个，所以它在三轮里每轮都检查了三十条子记录。把这些数字放在一起读，才不会误以为整个查询只看过十行。 ...

ANALYZE 更新的是 PostgreSQL 用来估算查询结果的统计模型。它不会重写查询、重建索引、回收死元组，也不承诺执行计划一定变快。它回答的具体问题是：规划器是否足够了解当前数据分布，能够合理比较不同访问路径？即使表里的有效行总数完全没变，一次批量状态更新也足以让原来的分布信息过时。先明确这个边界，才能判断刷新统计是否解决了眼前的问题。 统计描述的是什么 对于普通列，pg_stats 可以展示空值比例、常见值及其频率、不同值数量的估计和直方图等信息。规划器把它们与表的大小、成本参数一起使用。任务表中只有百分之一处于就绪状态，与百分之九十已经就绪，意味着完全不同的读取工作量。前者可能适合通过索引定位少量行，后者直接顺序读取整张表可能更合适。因此，“没有走索引”本身不等于优化失败，关键仍然是这个条件究竟会取出多少数据。 大表上的分析通常抽样，而不是逐行检查全部内容。更详细的统计需要更大的样本和更多工作，不能由“抽样”推导出“任何时候都很便宜”。即使业务数据没有变化，不同轮抽样产生的估计也可能略有波动。针对特定列提高统计目标，有助于更好地表达偏斜分布，但普通的单列统计不会因此自动学会所有列之间的关联。样本精度不足和模型缺少相关性信息，是两种不同的问题。 坏掉的样子 批量更新以后，旧的常见值频率可能严重低估过滤结果。规划器于是安排了实际需要重复很多次的嵌套循环，或者选择一个最终要读取大部分表内容的索引扫描。不过，查询本来就要返回大多数行时，顺序扫描是合理候选，不应该为了恢复过去的节点名称而强制索引。 还有一些变慢根本不是统计导致的。查询正在等待另一个事务释放锁时，重复分析不会把锁释放；统计刷新也不会消除应用连接池排队或凭空建立缺失的索引；所需顺序仍须通过排序或已有的有序路径提供。应当先找出计划中最早出现的有效行数偏差，再将它与真正耗时的环节对应起来，而不是把所有延迟都归结为统计陈旧。 快速验证 在可丢弃的 PostgreSQL 18 数据库中使用 psql 执行下面代码。实验把临时表放在事务里，最后回滚清理。这里特意使用临时表：自动清理进程不能分析其他会话的临时表，因此不会在两次受控观察之间悄悄刷新本实验的统计。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE jobs (id integer,ready boolean,payload text); INSERT INTO jobs SELECT g,g<=100,repeat('x',100) FROM generate_series(1,10000) AS g; CREATE INDEX jobs_ready ON jobs(ready); ANALYZE jobs; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready; UPDATE jobs SET ready=true WHERE id<=9000; SELECT most_common_vals,most_common_freqs FROM pg_stats WHERE schemaname=(SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()) AND tablename='jobs' AND attname='ready'; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready; ANALYZE jobs; SELECT most_common_vals,most_common_freqs FROM pg_stats WHERE schemaname=(SELECT nspname FROM pg_namespace WHERE oid=pg_my_temp_schema()) AND tablename='jobs' AND attname='ready'; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT id FROM jobs WHERE ready; DO $$ BEGIN IF (SELECT count(*) FROM jobs WHERE ready) <> 9000 THEN RAISE EXCEPTION 'data changed during analysis'; END IF; END $$; ROLLBACK; 在 PostgreSQL 18.6 的实测中，第一次查询通过 jobs_ready 估计并返回一百个就绪任务。接着的更新把就绪任务增加到九千个。再次分析之前，pg_stats 仍然给出 {f,t} 和 {0.99,0.01}，说明统计模型没有学到这次分布变化。表中存活记录的总数仍然是一万，变化的是满足条件的比例。 ...