16 深入专题：查询优化与执行计划
从扫描方法、连接算法到执行计划解读，系统梳理慢 SQL 的定位与优化路径。
48表JOIN的优化中, inner join 与 outer join 在连接顺序上有什么区别?
inner join 可以任意调整连接顺序, 而 outer join 不能随意调整顺序。因此优化时把 INNER JOIN 全部提前到最前面可获得更好性能。开启穷举法后 inner 被提前, 性能飙升; 关闭穷举(调小 join_collapse_limit)后 inner join 不再被优化器优化, 性能变差。DuckDB 对比中 PG 需要人工改写 join 顺序, 而 DuckDB 优化器能自动推理条件。
AQO(Adaptive Query Optimization)的核心思想是什么?
传统 CBO 基于统计信息估计行数, 但多表相关性、复杂谓词、数据倾斜、统计过期都会让估计误差跨 join tree 放大。AQO 的直觉是: 既然执行过类似 SQL, 把上次真实 actual rows 反馈给下次规划, 形成"优化器学习闭环"。AQO 核心目标限定在 cardinality estimation(行数估计), 不是直接生成计划, 最终是否换计划仍取决于 PG 原有 path 搜索和 cost model。它是 PG 扩展+源码补丁组合, 需重新编译并 shared_preload_libraries 加载。
Bao 学习型查询优化器如何工作?
Bao 是学习型查询优化器, 通过发出粗粒度查询提示(如 SET enable_nestloop TO off)引导 PostgreSQL 优化器, 用强化学习从错误中学习。由 Bao 服务器(独立 Python 应用)和 PostgreSQL 扩展两部分组成。
Bitmap Scan 解决什么问题? 与 Index Scan 的区别?
普通 Index Scan 的弱点是随机回表: 索引项相邻但指向的 heap tuple 分散, 返回几万行时随机读 heap page、重复访问同一 page、频繁 pin/unpin buffer 比顺序扫描还差。Bitmap Scan 用支持 amgetbitmap 的访问方法(btree/gin/gist/brin/hash/sp-gist), 执行期把命中 TID 放进内存 TIDBitmap, 再按物理顺序回表, 减少随机 IO。注意: PG 计划里的 Bitmap Heap Scan 不是原生磁盘 bitmap index。
CBO(基于代价优化)的核心流水线是什么?
CBO 以代价模型为核心: 1) 生成候选路径; 2) 估算每条路径输出多少行(基数); 3) 把 IO/CPU/排序/哈希/并行通信等成本折算成统一 cost; 4) 选择预计代价最低的路径。它牺牲规划时间、模型复杂度与可解释性, 用更多规划期开销换更低执行期开销。选择率 0.01% 时索引扫描很香, 80% 时索引回表可能比顺序扫描还差。
DuckDB 如何自动消除相关子查询? PostgreSQL 怎么办?
相关子查询可看作参数化子查询, PG 和 SQLite 不自动取消相关性, 优化器对每一行执行一次子查询, 记录越多越慢(如执行9000次)。DuckDB 优化器会自动消除相关子查询(rewrite)。PG 中改写相关子查询提升性能的方法: 1) 用窗口函数改写; 2) 用 JOIN 消除相关子查询。
GEQO(遗传查询优化器)解决什么问题? 何时使用?
当 join relation 数达到 geqo_threshold 阈值后, 穷举(动态规划)搜索空间指数级膨胀、规划时间过长, GEQO 用遗传算法(选择/交叉/变异)在有限规划时间内近似找到一个"足够好"的连接顺序, 牺牲优化精度换更快优化速度。仅当查询含大量表连接(通常超过4-5个)、优化时间过长、对计划精度要求不高时使用。很多"偶发慢SQL"是大 join 进入 GEQO 后探索到的 join order 质量变化导致。
HASH JOIN 算法的核心原理是什么? 内存存不下时怎么办?
hash join 假设一边(通常小表)作为 hash table, 由两片内存区域组成: 一片存地址、一片存真实 value(因为 value 可能变长)。内存存得下时直接构建+探测; 内存存不下时走分批(batch)的 hybrid hash join, 把 build/probe 侧拆成多批, 当前批留内存, 其余写临时文件。并行 hash join 同样面临 inner table 能否放进内存的问题。
Nested Loop Join 的工程价值是什么? 什么时候最强/最差?
NestLoop 不是"最笨的双重循环", 其工程价值是把外表当前行的值传给内表路径, 让内表用索引、参数化子查询、Memoize 或其他可重扫节点快速找匹配行。如果外表小、内表有合适索引, 往往是最强路径; 如果外表行数被低估、内表每次全扫, 会把错误放大成灾难。
On-Disk Hash Join 是什么? 为什么会出现 Batches: N?
当 hash join 的 build/inner 侧放不进 work_mem*hash_mem_multiplier 预算时, 走多 batch 的 hybrid hash join。EXPLAIN 里的 Batches: 32 说明不是纯内存 hash join, 而是把两侧拆成多批, 当前批留内存、后续批写临时文件。此时瓶颈从 CPU hash 计算变成临时文件 IO、批次数爆炸、数据倾斜、内存参数与并发放大。
PG 写表(insert into select / copy into)为什么还不支持并行?
PG 9.6 开始支持并行, 但 create table as/select into/materialized view 的并行只体现在后面的查询部分, 写入(insert)部分是单进程的 Gather。本质上要解决导入速度, 是系统工程: 即使解决了写入并行, WAL insert 可能又成为瓶颈, 网络存储(尤其云盘)串行写 IO 延迟瓶颈明显。
PG15 enable_group_by_reordering 如何提高多列分组聚合性能?
通过调整多列 group by 的列排序顺序, 提高 sort group 性能: 每一次排序尽可能让下一次排序涉及的行顺序调整更少。例如 group by a,b, 若 a 重复值太多应先排 b 再排 a(先排 a 等于白排)。实际场景还需多列统计信息支持, 让优化器选出更优组合, 如 a,b,c distinct 数相同时应排 c,a,b。
PG16 IO 统计信息大升级的意义?
重点升级 IO 统计颗粒度, 新的统计信息有助于判断如何优化 shared buffer、checkpoint 调度、bgwriter 调度、backend 刷盘等参数, 减少 backend 刷盘带来的 IO 延迟与 IO 操作。原来的统计颗粒度太大, 对参数优化参考价值不大。
PG16 enable_presorted_aggregate 解决什么?
PG16 新增 GUC enable_presorted_aggregate, 优化器支持预排序选择, 减少 distinct|order by agg 的显式排序消耗。开启后优化器会优先使用相应字段索引或在前段执行过程优先产出有序结果传输给聚合函数, 通常在字符串有序聚合、选择中位数等聚合时使用排序。
PG16 explain (GENERIC_PLAN) 的作用?
PG16 支持 EXPLAIN (GENERIC_PLAN) 直接打印带变量 SQL 的通用执行计划。generic plan 可理解为默认(根据数据柱状分布、偏大众的通用)执行计划: 用变量作条件时优化器假设输入是概率较大的值来生成计划。例如某值占 99% 时 where 字段等于某值会用 seqscan 而非索引。
PG16 extend relation 优化提升什么场景?
扩展数据文件瓶颈有三: 1) 扩展时在 shared buffer 申请 page, 无空闲时需驱逐 dirty buffer 触发 wal flush; 2) 扩展期间先写 zero page 再写实际内容(double write); 3) bulk 扩展只平摊了锁成本。优化用 smgrzeroextend() 扩展 page 不使对应 kernel page cache 变脏, 提升批量、高并发写入场景(IoT、时序、数据导入)性能。
PG16 为什么新增 Parallel Hash Full Join? 此前不支持的原因?
此前不支持 parallel full outer join 的原因是有死锁风险, PG16 增加 PHJ phase PHJ_BATCH_SCAN 处理死锁问题, 从而支持并行 hash full join。
PG16 优化 ORDER BY / DISTINCT aggregates 减少排序次数的思路?
未优化前每个聚合函数都要单独排序且不使用索引, 性能差。PG16 优化方法较暴力: 选出一种排序方法覆盖最多的 agg(可采用索引), 覆盖聚合排序数量一样多则选第一种, 并结合 incremental sort 减少排序次数。
PG16 优化器支持 Incremental Sort for DISTINCT 是什么?
PG16 让优化器在 DISTINCT 场景也支持 Incremental Sort(增量排序), 如果数据扫描过程已保证部分有序, 则只需对剩余部分排序, 减少大批量数据排序带来的 CPU 开销。
PG17 如何优化 wal insert lock 提升高并发写入吞吐?
通过原子操作减少 wal insert lock 锁冲突, 提升高并发写入吞吐性能。
PG17 用 Merge Append 提升 UNION 性能的原理?
UNION 有去重需求。原优化器把所有子查询结果放一起再处理, PG17 让子查询按 union 字段有序返回, 通过 Merge Append 在 merge 过程中去重(配合 Unique 节点), 有效提升性能。
PG17 的 JIT deform_counter 统计什么?
explain analyze 和 pg_stat_statements 增加 JIT deform_counter, 用于区分统计 tuple Deform(元组拆解) 和 ing expression(表达式计算)的耗时, 更细粒度定位 JIT 相关性能开销。
PG18 fast-path lock 优化了什么?
fast-path locks 是轻量级锁机制, 避免访问共享锁表(Lock Manager)提高性能, 存于 PGPROC。之前 FastPathTransferRelationLocks() 和 GetLockConflicts() 每次迭代都重新计算 fast-path group 并扫描空 group, 造成不必要开销。优化: 减少重复计算、跳过空的 fast-path group, 提升高并发下锁搜索效率。
PG18 numeric 乘法算法性能优化?
PG18 优化 numeric 类型乘法算法, 提升 decimal/numeric 运算性能。底层 native numeric 为支持科学计算采用可变长度存储支持超长精确数值, 导致性能下降, PolarDB 开源版有 fixeddecimal/pgdecimal(decimal128/decimal64)插件改进。
PG18 如何优化大量分区的规划(plan)阶段性能?
分区表查询时, 对每个未剪枝掉的子分区, 规划器为它克隆父表等价成员(Equivalence Member)并标记 em_is_child, 旧的实现把这些成员都加入父等价类, 子分区数量增加时查找变慢(二次方级别)。补丁改变存储和查找子分区等价成员的方式, 解决规划时间急剧变慢的问题。
PG18 如何利用 tuple_fraction 优化分区表 Append 节点?
Append 节点合并各分区数据, 之前决定如何扫描每个分区时未充分利用 tuple_fraction(查询只需返回多少比例的元组)。补丁让 Append 累积子路径时考虑 tuple_fraction: 返回结果较少时更积极使用 index scan 和参数化 nestloop, 选择"分数分支"子路径; 需要全部元组时仍选"非分数分支"。
PG18 如何打印正在执行的慢SQL的执行计划?
PG18 内置 pg_log_query_plan(pid) 函数, 可请求将指定 backend 进程当前正在运行的查询计划以 LOG 级别写入服务器日志(不发给客户端)。注意: 语句在函数内执行时只记录最深层嵌套查询的计划; 子事务 abort 后无法记录。此前需要 pg_show_plans、pg_query_state 等插件实现。
PG18 如何用扩展统计信息提高 hash join bucket 估算准确度?
哈希连接中准确估计哈希桶大小至关重要, 桶数估少会导致哈希冲突增加、降低效率甚至误选 merge join。多列连接时列间存在函数依赖(如城市对应邮编), 若忽略依赖会低估/高估 distinct 值。Commit 6bb6a62f 增加额外阶段: 对包含两个以上 join 子句的情况查找扩展统计信息(CREATE STATISTICS … ndistinct ON x,y,z), 将 join 子句按关系分组做多列估计, 得到更准确的 distinct 值数量, 从而精确估计桶大小。
PG18 子查询单列 GROUP BY 统计信息提升的原理?
当子查询只有一个 GROUP BY 列时, 输出变量可视为唯一(unique), 优化器利用这一特性在上层查询做更精确的统计估计, 改善对子查询输出行数和数据分布的估计, 避免低估/高估导致次优计划。
PG18 并行 nestloop join 的优化是什么?
PG18 在并行 nestloop join 中优先考虑物化最廉价的 inner path(物化内表), 减少每个 worker 重复扫描内表的代价。
PG18 异步IO(io_uring)带来什么性能提升?
PG18 引入 Linux 特有的 io_uring 机制, 异步 IO 最大的差别是 IO 等待模式: 某些不必等待 IO 返回即可继续的场景, 同步 IO 会阻塞等待。用 nvme 盘(30微秒内)感知小, 但云存储一次 IO 延迟在毫秒级(相差2个数量级), 异步 IO 让云环境重 IO 负载(如 OLAP 查询)性能飙升。PG18 中 AIO 限于读操作, 写保持同步。
PG18 支持重置/设置指定对象统计信息的作用是什么?
pg_upgrade 大版本升级只导元数据不导统计信息, 升级后需 analyze 重新生成, 若生成前业务大量访问可能计划不准; 数据重大变化时统计更新不及时也会计划不准。PG18 增加 pg_clear_relation_stats/pg_set_relation_stats(对象级)及 pg_clear_attribute_stats/pg_set_attribute_stats(列级)函数, 用于重置和设置指定对象统计信息, 为导出/导入/固化统计信息铺路。
PG18 的 Self-Join Elimination 是什么?
Self-Join Elimination 是针对"烂SQL"的一个优化补丁, 当 SQL 中表与自身做 join(自连接)且该 join 不影响结果时, 优化器可以消除多余的自连接, 避免不必要的扫描。
PG18 索引启发式扫描如何优化 in/=any(array) 多值匹配?
处理 WHERE column = ANY(array)/column in(…) 时, 原始逻辑对数组每个元素生成扫描键, 若下一个匹配元组在后续页面会立即结束当前扫描并重新从 btree root 下探。当数组元素集中在相邻叶子页时(如 in (1,2,3,4))频繁重启扫描浪费 CPU 和 IO。PG18 启发式扫描: 若原始扫描已从初始叶子页移动到相邻页(说明匹配密集), 不立即结束, 直接在叶子节点步进。
PG19 EXPLAIN (IO) 能观测到什么?
这组 commit 在 SQL 层(EXPLAIN ANALYZE IO)和 auto_explain(log_io)暴露 ReadStream 内部 IO 统计: 扫描节点预取了多少数据、实际 IO 请求次数、等待次数、平均预读深度等, 使 DBA 能量化预读效率、识别预读不足或过度预读, 有据可查地调整 effective_io_concurrency/effective_cache_size 等参数, 不再依赖经验猜测。
PG19 ExecProcNodeInstr 内联化优化了什么?
EXPLAIN (ANALYZE, BUFFERS) 会为每个计划节点挂 ExecProcNodeInstr 包装器, 在每个元组经过节点前后记录时间/缓冲区统计, 开启性能分析本身引入显著开销(海量数据可达 10% 以上)。Commit 54400028 将其及核心函数移至同一编译单元并标记 inline, 编译器生成更优代码, 降低 4%~12% 的 EXPLAIN ANALYZE 性能开销, 减少观测副作用。
PG19 TSC 计时优化如何降低 EXPLAIN ANALYZE 开销?
EXPLAIN ANALYZE/TIMING ON 传统依赖 gettimeofday 等系统调用, 每次调用涉及用户态到内核态切换, 处理大量行时计时开销可达 2 倍。PG19 在 x86-64 上用 CPU 时间戳计数器(RDTSC/RDTSCP)替代系统调用, 开销从 2 倍降到 1.2 倍, TPC-H 的 InstrShowTime 减少约 20%, 并通过 timing_clock_source GUC 允许选择计时源, pg_test_timing 验证选择。
PG19 pg_stat_lock 的设计为什么"克制"?
PG19 新增 pg_stat_lock, 首次把锁行为沉淀为可累计、可重置、可对比的统计事实, 解决过去只能"看现场"(pg_locks 等)不能"看趋势"的问题。它只做 cluster 级、按 LockTagType 聚合的统计(5列), 不做按表/按SQL/按会话的高基数明细, 因为锁管理是数据库最敏感的核心路径, 高基数强实时明细会把代价打在并发控制路径上, “为了监控锁先把锁搞慢”。
PG19 pg_stat_statements 如何归一化 FETCH 语句?
此前每个不同 FETCH 调用(即使只差提取行数, 如 FETCH 1 c1 与 FETCH 2 c1)都生成唯一 queryId, 大量游标场景产生大量几乎重复条目。Commit bee23ea 将 FETCH 的大小显示为常量归一化, 使不同 FETCH 合并为同一 queryId, 优化游标 SQL 统计的实用性和准确性。
PG19 pg_stat_statements 新增 generic_plan/custom_plan 计数器解决什么?
带参数 SQL 可能走通用计划(Generic Plan, 复用同一计划)或定制计划(Custom Plan, 按每次参数单独生成)。此前 pg_stat_statements 只统计执行次数/耗时, 无法区分走了多少次通用计划、多少次定制计划。新增两个计数器后可分析计划类型分布, 帮助判断参数是否导致计划抖动、是否需要 plan_cache_mode 调整。
PG19 为什么 ANTI join 且内表唯一时可启用 Memoize?
Anti join(反连接)用于查找"在一个表存在但在另一个表不存在"的记录, 通常通过 NOT EXISTS / NOT IN / LEFT JOIN…WHERE IS NULL 实现。Commit 0da29e4 允许 ANTI join 且内表唯一时启用 Memoize 算子: 内表唯一意味着同一参数只产生一个结果, 可以用 Memoize 缓存探测结果, 避免重复探测内表, 提升性能。
PG19 为什么"异步IO退化成同步IO"反而性能飙升?
io_method=worker 模式下所有后端进程共享一个提交队列, 由全局锁 AioWorkerSubmissionQueueLock 保护。高并发时锁竞争成为新瓶颈, 等待锁的时间甚至超过 IO 本身。Tomas Vondra 的补丁用条件性锁定+智能退化: 如果不能立即获得锁, 就放弃异步直接执行同步 IO, 避免所有进程排队抢同一把锁。
PG19 外键检查快速通道如何绕过 SPI 提升性能?
外键检查传统用 SPI 执行 SELECT … FOR KEY SHARE 验证引用完整性, 每行都有完整 CCI(命令计数器递增)和安全上下文切换。快速路径绕过 SPI 直接探测索引: CCI 和安全上下文切换对整个批次只执行一次(批大小 RI_FASTPATH_BATCH_SIZE=64), 因为单行 CCI 不必要、每行探查完全以 PK 表所有者身份运行。
PG19 如何根治 NOT IN 的性能问题(16年顽疾)?
NOT IN 因 NULL 语义(子查询返回 NULL 时整个表达式为假)导致优化器长期无法优化为反连接(anti join), 只能生成 SubPlan: 对外层每一行都执行一次子查询, 无法做全局连接顺序优化, 无法利用 hash/merge join, 索引几乎失效。Richard Guo 提交的补丁解决了 NULL 障碍, 使 NOT IN 可优化为高效反连接, 10万行主表场景下 SubPlan 450ms 以上降到哈希反连接 50ms 以下(约9倍), 百万级时差异可达分钟级 vs 秒级。
PG19 的 Eager Aggregation(急切聚合)是什么优化?
Eager Aggregation 是在 JOIN 之前尽可能早地对某个表或子查询做部分聚合(partial aggregation), 再参与后续连接, 最后顶层完成完整聚合, 以减少中间数据量。例如先对 b 按 b.a_id 做 SUM(b.y) GROUP BY b.a_id 再与 a join, 而非先全量 join 再聚合。由 Richard Guo(郭峰) 重构实现, 2017 年 Antonin Houska 提出原型。
PG19 的 TID Range Scan 并行解决了什么问题?
此前规划器在"并行顺序扫描"与"非并行 TID 范围扫描"间权衡: 并行 seqscan 有 CPU 并行优势但可能多读磁盘块, 非并行 TID 范围扫描只读所需块但缺 CPU 并行。PG19 引入并行 TID 范围扫描后, 直接获得并行 CPU 优势同时只扫描需要的块, 减少不必要 IO。其思想与 PolarDB epq 一样: 动态分配数据扫描范围, 多劳多得, 不会因某个并行任务慢而拖慢整体。
PG19 的 planner hook 扩展为未来做什么准备?
三个 patch: 1) 给 pg_plan_query()/planner() 增加 ExplainState 参数; 2) 新增 planner_setup_hook/planner_shutdown_hook; 3) 给 PlannedStmt 增加 extension_state 成员。为增强扩展能力、细粒度计划器调试、接入更多外部优化器/自定义优化器铺路。
PG19 组提交(group commit)可观测性补丁做了什么?
高并发小事务下, 每个事务 commit 都要刷 WAL 会打爆 IO, PG 用组提交把多个同时提交的事务聚集到一起减少物理 IO。但"多少个事务等待、等多久"难定。PG19 补丁在 WAL flush 前的组提交延迟等待期间报告一个等待事件(wait event), 使组提交是否生效、等待多久可观测, 帮助判断 commit_delay/commit_siblings 等参数设置是否合理。
PG19 组提交等待事件与 pg_stat_lock 分别补上哪些可观测短板?
pg_stat_lock 补上锁等待的"增量统计"短板(按 lock type 聚合, 可累计可对比); 组提交补丁补上 WAL flush 前组提交延迟等待的观测短板, 两者都让 DBA 从"有现象无累计事实"转向"有趋势画像"。
Parallel Hash Join 的难点是什么?
难点在共享: 每个 worker 各建一份 hash table 简单但重复 build 侧 CPU 和内存; 共用一张 hash table 则需要用 barrier(屏障) 同步协调多个 backend 协作构建共享哈希表、分配 batch、避免互相踩踏。真正的性能重点不是开更多线程, 而是 hash table 共享方式、分区粒度、cache/TLB、本地内存、同步、内存带宽与数据倾斜之间的平衡。
PostgreSQL 并行聚合(Parallel Agg)的三个阶段是什么?
并行聚合把聚合拆成 Partial Agg(worker 侧局部聚合, 把 N 行输入压缩成 G 个局部状态)、Combine Agg(合并各 worker 的部分状态)、Finalize Agg(把合并后状态转成最终结果)。核心思想是 worker 先做局部聚合, 再交给 leader 合并, 避免 leader 单进程 GROUP BY 成为新瓶颈。要求聚合函数必须有正确的 combinefn(如 avg 需合并 sum/count 而非平均值)。
PostgreSQL 的 Merge Join 需要同时回答哪些问题?
Merge Join 不是简单"两边排序后拉拉链合并", 至少同时回答: 1) 哪些 join 条件能作为 merge 条件; 2) 两侧输入如何获得同一套排序语义; 3) 遇到重复 key 时 inner 已扫过的匹配段如何重新使用(mark/restore); 4) outer join/semi join/anti join/NULL key 如何保持语义; 5) 排序、mark/restore、Materialize、重复 key 重扫的代价由谁估算。看 EXPLAIN 时要注意上游是否有 Sort、inner 是否有 Materialize。
PostgreSQL 的 pg_dropcache 和 pg_buffercache_evict 有什么用？
pg_dropcache 是清理操作系统页缓存的插件（drop cache），pg_buffercache_evict 是驱逐 shared buffer 的函数。两者都用于测试查询在“无缓存”状态下的真实性能（冷启动性能），或模拟缓存失效。它们让 DBA 能精确控制缓存状态，评估缓存命中率对性能的影响，是性能测试的辅助工具。
PostgreSQL 脏页通过哪三种机制刷新?
更新/插入先在 shared buffers 内存修改并标记脏页, WAL 先落盘, 表文件延后更新。脏页通过三种机制刷新: 1) 检查点(checkpoint); 2) 后台写入器(background writer, 后台写脏页保持干净缓冲区可用); 3) 用户后端进程刷脏(当后端需要干净 page 但 buffer 不够时自己刷)。
PostgreSQL 自适应并行扫描(Parallel Seq Scan)如何分工?
多个参与进程通过共享扫描描述符动态领取连续 block chunk: 快的进程领取更多 chunk, 慢的进程不阻塞其他进程; 扫描末尾减小 chunk size 降低长尾。它是运行时领取任务, 而非计划阶段静态平均切段, 避免"每个 worker 从头扫一遍"和"慢者卡住整体"。
RBO 与 CBO 的关系? PostgreSQL 主优化器是哪种?
RBO(基于规则优化)用固定规则缩小搜索空间, 如谓词下推、常量折叠、能用索引就用索引。但真实负载中同一规则在不同数据分布下可能完全相反。PostgreSQL 主优化器不是纯 RBO, 而是以规则生成和裁剪候选路径、再用统计信息与代价模型选择路径的 CBO 系统。
SQL 时快时慢(例如 select count 主键 1500万行 要5分钟)怎么排查?
可能原因: 计划、资源、锁、脏数据相关问题, 特别是 IO 或并行计算时的 CPU 资源, 极少是锁冲突。方法: 打开 auto_explain 跟踪执行计划与 io timing; auto_explain 只能跟踪单一 SQL, 要看执行过程中的环境问题可结合 perf insight 思路间歇性采集会话状态(如 pgsentinel 插件), 类似 AWS performance insight 的理念。
TID 扫描路径(tidpath.c)的核心组件是什么?
TID 是行号, 由 blockNum 和 ItemPoint 组成, 如 (103,21) 表示第103号数据块第21行。核心组件: TidPath(直接通过 TID 访问元组)、TidRangePath(通过 TID 范围条件扫描)、RestrictInfo(存储查询条件)。入口 create_tidscan_paths 生成普通/范围/参数化三种 TID 扫描路径; TidQualFromRestrictInfo 分析单个条件是否可用于 TID 扫描。
explain 执行计划结果如何可视化?
可用 dalibo 开源的 pev2 工具(https://github.com/dalibo/pev2), 支持在线使用 explain.dalibo.com、下载单个 index.html 本地离线使用、或整合到 web 应用, 把 JSON 格式的 explain 计划渲染成可视化树形图。
fetch with ties 如何解决分页优化 gap 问题?
limit offset 翻页到很后面时, offset 需扫描并丢弃大量记录, 性能差, 常用"位置偏移条件"优化但排序列有重复值会出现 gap。fetch first with ties 新标准: 返回时可超过 limit 数, 把最后一条相同排序值的行都返回, 避免 gap(老标准 limit 需引入 pk/uk 解决)。担心数据倾斜一次返回过多可: 1) 用游标; 2) 把排序字段改成表达式(如加 row 的 hash 值), 减少重复值个数。
io_max_combine_limit 与 io_combine_limit 的区别?
io_combine_limit 控制单个 IO 操作可合并的最大块数(软限制, 最大值增至 1MB); io_max_combine_limit 是新增的硬限制, 用于限制异步 IO 分配共享内存时每个块的数据大小, 减少 AioHandleIov/AioHandleData 内存占用, 为后续扩大 PG_IOV_MAX 做准备。
optimizer trace 的作用是什么?
帮助理解优化器的优化原理, 生成执行计划的决策过程(parser/rewriter/optimize), 辅助诊断因优化器自身、参数设置、代价校准因子、索引缺失、统计信息不准带来的 SQL 性能问题, 适合 DBA 和开发者。参考 opttrace、hyper-db 等工具。
or_to_any_transform_limit 参数控制什么?
控制 OR 到 ANY 转换: 当 OR 表达式中参数长度超过该阈值(默认5)时, 优化器尝试查找并分组多个相似的 OR 表达式为 ANY 表达式, 基于变量侧的等价性(一侧为常量、另一侧为变量)。好处是更快的规划和执行, 某些情况产生单次索引扫描而非多次 bitmap scan; 但 distinct OR 较多时可能导致规划退化, -1 完全禁用。
partitionwise join 和 partitionwise aggregate 的触发条件是什么？
partitionwise join：两个 JOIN 的分区表在 JOIN 字段上分区、分区类型一致（枚举/LIST/范围/HASH）、分区个数一致，且 JOIN 字段类型一致，优化器就选择并行分区智能 JOIN，子分区各自 JOIN 子分区（10 亿 join 10 亿 from 1006 秒降到 76 秒）。partitionwise aggregate：分区表聚合的分组字段为分区字段时，选择并行分区智能聚合（10 亿 from 191 秒降到 8 秒）。两者都通过 enable_partitionwise_aggregate / enable_partitionwise_join 控制，配合 parallel append 让分段结果集尽量小以提升性能。
pg_dump 导出统计信息功能做了哪些优化?
PG18 支持统计信息导出/导入, 解决大版本升级后需 vacuum analyze 重新生成统计信息的问题。后续 patch 优化了 pg_dump 导出统计信息大量占用内存的问题, 并通过批量查询大幅降低获取统计信息的总时间。
pg_hint_plan / pg_plan_inspector / pg_plan_advsr / pg_store_plans 分别是什么?
四者用于复杂SQL执行计划优化修正: pg_hint_plan 手动强制某些执行计划决策; pg_plan_inspector 外部框架, 通过 explain analyze 获得真实统计信息存外部, 下次执行 feedback 给优化器修正计划(机器学习方法); pg_plan_advsr 内部实现, 不存统计信息而存修正后的 SQL HINT, 相当于内部自动通过 HINT 修正计划; pg_store_plans 像 pg_stat_statements 一样存储执行计划。
pg_hint_plan 新增的 DisableIndex hint 是什么?
pg_hint_plan 支持 PG18 后新增 DisableIndex hint: 在查询规划时排除指定索引(全部、少部分或正则匹配的索引名), 优先级高于其他 hint, 被禁用的索引即使被 IndexScan 显式请求也不会使用。
pg_overexplain 插件为 EXPLAIN 增加了什么?
pg_overexplain 为 EXPLAIN 增加两个调试选项: EXPLAIN (DEBUG) 提供更详细调试信息、EXPLAIN (RANGE_TABLE) 显示范围表信息, 解决 debug_print_plan 输出过于冗长的问题, 帮助开发者深入分析查询计划。
pg_plan_advice 三件套解决什么问题?
解决"优化器抽风时无法精确干预"的悖论: enable_seqscan=off 太粗暴影响其他查询, pg_hint_plan 复杂 JOIN 语法难精确控制。pg_plan_advice 及配套 pg_collect_advice/pg_stash_advice 提供捕获、审视、修改并强制执行查询计划关键决策的能力, 遵循"机制与策略分离", 基于更完美的信息(实际执行经验)做人工干预。
pg_profile 是什么? 解决什么问题?
pg_profile 是 PostgreSQL 性能基准对比/诊断工具(https://github.com/zubkov-andrei/pg_profile), 弥补 PG 性能诊断工具较弱的短板, 通过采集统计快照做基准对比, 大大提升排查问题的效率。
pg_qualstats 的用途和局限?
pg_qualstats 采集 WHERE 条件统计用于推荐索引。测试发现其推荐 btree 索引较理想, 但 gin/gist/brin/bloom/sp-gist 等接口不理想; 它另一个核心价值是采样并存储过滤性较差的 SQL, 充当"发现过滤性差的 SQL"的角色, 即使没自动推荐索引, DBA 也可用自己的知识优化。配合 HypoPG 使用。
pg_stash_advice 持久化机制如何工作?
此前 pg_stash_advice 数据存于 DSA 动态共享内存, 重启丢失。Commit c10edb10 引入磁盘持久化: 新增 pg_stash_advice.persist 和 persist_interval 两个 GUC, 通过后台工作进程定期把 queryId→advice_string 映射写入数据目录下 pg_stash_advice.tsv, 重启后自动加载, 实现"一次配置永久生效"。
read_stream 预读逻辑优化了什么?
read_stream.c 负责在合适时候向内核发 read-ahead advice 优化 IO。之前实现过早放弃发建议, 导致后续读取可能遇到本可避免的 IO stall。优化后改进密集流(dense streams)的预读建议机制; 又简化距离启发式(distance heuristics), 为异步 IO 做准备。
什么是 semi-join / anti-join? 数据库未实现时如何模拟?
等值 JOIN 的表达式存在重复值且只需要 JOIN 字段/表达式时, 只查每个值第一条即可跳到下一个值, 常用来优化 in/exists/not exists/=any()/except 等。若数据库未实现半连接, 可用 递归/group by/distinct on/distinct 模拟。实测用递归模拟 SEMI-JOIN, 在 b 表 100万行只有 11 个唯一值的场景下, 改写后从 226.630ms 提升到 0.246ms。
分页优化的三种模型及其代价边界?
- LIMIT/OFFSET: 数过前 N 行再返回后 M 行, 深分页时被跳过的行仍经历扫描/排序/可见性判断, 只是没返回; 2) 服务端游标: 在会话/事务里保存执行状态分批取, 但把应用状态绑在连接和事务上; 3) keyset/seek method(每次递进输入 WHERE 边界): 把上一页最后一行排序键作下一页谓词, 索引从边界继续读, 但失去任意跳页能力。核心是把问题从"我要第几页"改写成"下一批从哪里开始, 能否用索引定位"。
向量搜索 limit + 距离阈值 where 为什么性能骤降?
向量搜索通常取 TOP N(limit 10)走索引很快。但加 where 距离阈值条件后, 优化器不知道距离操作符的"排序"和"小于"步调一致: 满足条件的记录够 limit 条时没问题, 不足 limit 条时优化器会扫完整个索引直到找不到 N 条, 性能骤降。GiST 索引同样问题。vectorchord 提供 similarity filter 算子, 把距离条件下推至向量索引, 超出距离立即停止搜索。
向量搜索优化的三板斧(空间/性能/召回)如何平衡?
向量搜索是近似求解, 在"空间、性能、召回"三方面取平衡, 类似 CAP 无法既要又要。先天优化: 降低维数(128→64)、降低精度(float32→float16/int/bit), 控制空间占用并逼近能容忍的最小召回率; 后天优化: 调整参数(如 ef_search), 在满足召回条数下逼近最小召回率。
如何查看 prepared statement 的 generic plan?
prepared statement 参数用位置变量替代, 直接 explain 会报错。PG12 起通过 plan_cache_mode 控制计划选择: generic plan 是适合大多数输入值的通用计划(数据倾斜大时可能不适合某些值), custom plan 按实际输入选最佳。要得到 generic plan 可用 generic-plan 等工具或 EXPLAIN (GENERIC_PLAN)(PG16 内置)。custom plan 需实际输入条件才有意义, 通常用 auto_explain 追踪。
数据分布与扫描方法如何影响 join+order by limit 的性能?
数据的组织+扫描方法决定数据过滤多少与性能极限, 即用索引精准定位且要求完全无 filter。例: gid 1~10 各10万行连续分布, crt_time 顺序写入, 查 gid=9,10 按 crt_time 排序。若按 crt_time 索引顺序扫需过滤80万行无用记录; 优化器选择大表作内表(过滤性更好)、配合 merge sort 才能高效。PG 允许大表作内表, MySQL 则需 STRAIGHT_JOIN 固定。
数据库整体变慢/慢SQL/性能抖动这三类问题, 分别该怎么分析和优化?
分三类处理: 1) 单一慢SQL: 从执行计划入手, 看计划是否正确、索引是否缺失/不合理、SQL是否需要改写、表是否有膨胀、是否存在锁冲突、SQL是否过于复杂需要固定计划或更高级优化器; 常用工具为 explain analyze、auto_explain、pg_stat_statements。2) 数据库整体变慢: 从资源瓶颈入手(IO/CPU/内存/锁/连接数)。3) 性能抖动: 结合间歇性采集会话状态(如 pgsentinel)、perf 等方法定位瞬时环境问题。同时强调"治未病", 在变慢前就做好监控与建模。
查询所有传感器最新值的典型慢SQL, 如何通过递归CTE优化?
场景: 查询所有传感器上报数据的最新值, 原方案用外部排序(external merge Disk)且扫描大量数据块, 耗时数千毫秒。优化1: 建 gid,crt_time desc 索引, 避免外部排序但仍大量扫描。优化2: 引入递归查询(CTE recursive, 索引链表跳跳糖), 扫描从几十万个 block 降到 47 个 block, 同时避免排序, 整体耗时从 5508 毫秒降到 0.6 毫秒。
编译器 PGO 如何让 PostgreSQL 性能飙升?
PGO(Profile-Guided Optimization)利用程序实际运行时的性能数据指导编译器优化, 相比静态 -O2/-O3 更有针对性。流程: 插桩编译→收集 profile→基于 profile 重新编译。优化点: 分支预测、代码布局、内联、循环优化、去除冷代码、虚函数去虚化、间接调用优化。云 RDS sysbench 结果优于自建可能与此相关。
自定义统计信息包含哪几层? 解决什么问题?
解决"优化器把列之间、表达式结果、热门组合当成独立且均匀"的问题。四层: 1) 调整单列统计目标(ALTER TABLE … SET STATISTICS); 2) CREATE STATISTICS 创建扩展统计(dependencies/ndistinct/mcv/表达式统计); 3) 用 pg_stats/pg_stats_ext/pg_mcv_list_items() 检查; 4) 用 pg_restore_relation_stats 等临时恢复(会被 autovacuum/analyze 覆盖)。例: tenant_id 与 currency 相关, 当成独立相乘会严重低估/高估行数。
软删除场景 NOT EXISTS 比 EXISTS 快 32 倍的根因?
当"少数派状态"(如 deleted=true 占很小比例)存在时, 用 NOT EXISTS 检查"是否不存在少数派"比 EXISTS 确认"多数派存在"更快, 因为查的是少数派索引, 且大部分查找结果在索引里找不到。对 PG 而言, “索引里找不到通常可直接结束; 找到了反而往往还得回表”, 这正是性能差距根因。