纲要EXPLAIN与EXPLAIN ANALYZE—— 执行计划的生成与解读执行计划的树形结构 —— 自底向上、从左往右的阅读原则成本模型Cost Model ——cost、rows、width的含义成本参数 ——seq_page_cost、random_page_cost、cpu_tuple_cost、cpu_index_tuple_cost、cpu_operator_cost统计信息Statistics ——pg_stats视图、histogram_bounds、most_common_vals、most_common_freqs行数估算Row Estimation —— 选择率Selectivity的计算原理多元统计信息Extended Statistics ——CREATE STATISTICS解决关联列估算失真执行计划可视化工具 —— PEV2、Depesz、PGBadger执行计划理解 PostgreSQL 如何执行查询在关系型数据库中理解执行计划是 SQL 性能优化的基础。PostgreSQL 的查询优化器Planner为每一个查询生成一个执行计划Execution Plan该计划定义了数据库访问数据的具体方式[reference:0][reference:1]。掌握执行计划的阅读方法是排查慢查询、进行 SQL 调优的必备技能。执行计划的结构与阅读规则PostgreSQL 的执行计划是一个树形结构Tree of Plan Nodes[reference:2]。每个节点代表一个具体的操作——扫描表、排序、聚合、连接等。阅读执行计划需要遵循两条核心原则自底向上Bottom-Up下层节点的输出作为上层节点的输入。从左往右Left-to-Right同一层级中先执行左边的节点。以一个典型的执行计划为例Sort(cost163.67..168.14rows1787width14)-HashAggregate(cost67.23..85.10rows1787width14)-Seq Scanoncustomers(cost0.00..34.19rows1719width14)在这个计划中Seq Scan on customers是最底层节点它顺序扫描customers表并将结果行传递给上层HashAggregate节点进行哈希聚合最终由Sort节点完成排序[reference:3]。EXPLAIN 输出字段详解一个标准的EXPLAIN输出包含以下关键字段字段含义cost预估成本格式为startup_cost..total_costrows预估返回的行数width预估每行的平均字节数actual timeEXPLAIN ANALYZE实际执行时间毫秒actual rowsEXPLAIN ANALYZE实际返回的行数loopsEXPLAIN ANALYZE节点被执行的次数BuffersEXPLAIN (BUFFERS)缓冲区命中与读取情况Planning Time规划阶段耗时Execution Time执行阶段总耗时EXPLAIN输出中的cost是优化器选择执行计划的核心依据[reference:4]。startup_cost表示返回第一行之前的启动成本total_cost表示返回所有行的总成本[reference:5]。对于大多数查询优化器以最小化total_cost为目标但在EXISTS子查询等场景中优化器会优先选择startup_cost最小的计划[reference:6]。成本模型优化器的决策依据PostgreSQL 的优化器通过成本模型Cost Model来评估不同执行路径的代价并选择成本最低的方案[reference:7]。成本是一个无量纲的相对值measured in arbitrary units其绝对值没有物理意义只有相对值影响优化器的决策[reference:8]。核心成本参数PostgreSQL 提供了一系列 GUC 参数来控制成本计算[reference:9]。以下是最关键的几个参数默认值含义seq_page_cost1.0顺序读取一个数据页的成本[reference:10]random_page_cost4.0随机读取一个数据页的成本[reference:11]cpu_tuple_cost0.01处理每一行的 CPU 成本[reference:12]cpu_index_tuple_cost0.005索引扫描中处理每个索引条目的 CPU 成本[reference:13]cpu_operator_cost0.0025执行每个操作符或函数的 CPU 成本[reference:14]默认情况下random_page_cost是seq_page_cost的 4 倍[reference:15]。这一设定基于传统机械硬盘HDD的物理特性——随机 I/O 远慢于顺序 I/O。成本参数的调优实践随着存储技术的发展SSD、NVMe随机 I/O 与顺序 I/O 的性能差距已大幅缩小。因此在生产环境中调优random_page_cost是常见的优化手段SSD建议设置为1.52.0NVMe建议设置为1.11.3-- 查看当前成本参数SELECTname,setting,unitFROMpg_settingsWHEREnameLIKE%cost%;-- 调整 random_page_cost需 superuser 权限ALTERSYSTEMSETrandom_page_cost1.5;SELECTpg_reload_conf();需要注意的是random_page_cost不应低于seq_page_cost[reference:16]。如果数据库完全缓存于 RAM 中可将两者设为相等[reference:17]。统计信息行数估算的数据基础优化器要做出准确的成本估算必须依赖统计信息Statistics。PostgreSQL 通过ANALYZE命令或后台autovacuum进程收集表和列的统计信息[reference:18]。核心统计信息视图pg_statspg_stats视图提供了对pg_statistic系统表中统计信息的可读访问[reference:19]。关键字段包括字段含义null_frac列中 NULL 值的比例avg_width列值的平均字节宽度n_distinct估算的不同值数量most_common_vals最常见值的列表most_common_freqs最常见值对应的频率histogram_bounds直方图边界值-- 查看表的统计信息SELECTattname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_boundsFROMpg_statsWHEREtablenameyour_tableANDattnameyour_column;选择率的计算选择率Selectivity是 WHERE 条件过滤后返回的行数占总行数的比例[reference:20]。PostgreSQL 根据统计信息计算选择率再用选择率乘以表的总行数reltuples来自pg_class得出估算行数[reference:21]。等值条件column value如果该值出现在most_common_vals中直接取对应的most_common_freqs作为选择率[reference:22]。范围条件column value通过histogram_bounds直方图进行插值计算[reference:23]。例如直方图将数据分为 100 个等频桶目标值所在的桶位置决定了选择率。统计信息的时效性过期的统计信息会导致优化器做出错误决策。可通过pg_stat_all_tables视图检查统计信息的收集时间SELECTschemaname,tablename,last_analyze,last_autoanalyzeFROMpg_stat_all_tablesWHEREtablenameyour_table;如果last_analyze或last_autoanalyze时间过早说明统计信息可能已过时建议手动执行ANALYZE。多元统计信息解决关联列估算失真PostgreSQL 优化器默认假设不同列之间的条件是相互独立的[reference:24]。对于多列条件如WHERE col1 a AND col2 b优化器会将各列的选择率相乘得出总选择率。-- 假设两列的选择率均为 0.08-- 总选择率 0.08 × 0.08 0.0064-- 若表有 10000 行估算行数 64-- 但实际满足两条件的有 8000 行 → 严重低估当列之间存在函数依赖Functional Dependency时这种独立性假设会导致估算严重失真[reference:25]。PostgreSQL 从 10 版本开始引入了多元统计信息Extended Statistics来解决此问题[reference:26][reference:27]。CREATE STATISTICSCREATE STATISTICS命令用于创建扩展统计信息对象[reference:28]。支持的统计类型包括[reference:29][reference:30]ndistinct—— 多列组合的不同值数量统计dependencies—— 函数依赖统计mcv—— 多列最常见值列表-- 创建函数依赖统计信息CREATESTATISTICSstat_place_community(dependencies)ONplace_name,community_nameFROMyour_table;-- 分析表以生成统计信息ANALYZEyour_table;-- 查看已创建的统计信息对象SELECT*FROMpg_statistic_ext;创建多元统计信息后优化器在进行多列条件估算时会使用更精确的选择率避免将两个相关列的选择率简单相乘[reference:31]。版本支持说明功能引入版本多元统计信息ndistinct、dependencies、mcvPostgreSQL 10表达式统计信息Expression StatisticsPostgreSQL 14在 PostgreSQL 14 及更高版本中CREATE STATISTICS还支持基于表达式的统计信息收集[reference:32]。执行计划可视化工具当执行计划非常复杂数百行时纯文本阅读效率低下。以下工具可将执行计划可视化帮助快速定位高消耗节点PEV2PostgreSQL Explain Visualizer 2PEV2 是一个基于 Vue.js 的执行计划可视化组件[reference:33]。在线版https://explain.dalibo.com/ [reference:34]GitHubhttps://github.com/dalibo/pev2 [reference:35]PEV2 的核心价值在于图形化展示将树形结构以节点图呈现点击节点可查看详情耗时占比直观显示每个节点占总执行时间的百分比估算偏差标注自动标记underestimate低估和overestimate高估的节点其他工具Depeszhttps://explain.depesz.com/老牌执行计划分析工具提供索引建议PGBadger日志分析工具可汇总执行计划信息API 速览EXPLAIN所属PostgreSQL SQL 命令语法EXPLAIN[(option[,...])]statement常用选项ANALYZE实际执行语句并显示真实执行统计[reference:36]BUFFERS显示缓冲区使用情况[reference:37]COSTS显示成本估算默认开启[reference:38]VERBOSE显示额外详细信息[reference:39]FORMAT { TEXT | XML | JSON | YAML }输出格式[reference:40]示例EXPLAIN(ANALYZE,BUFFERS,COSTS)SELECT*FROMordersWHEREcustomer_id12345;CREATE STATISTICS所属PostgreSQL DDL 命令语法CREATESTATISTICS[IFNOTEXISTS]statistics_name[(statistics_kind[,...])]ONcolumn_name,column_name[,...]FROMtable_name;statistics_kind 取值ndistinct多列组合的不同值数量dependencies函数依赖mcv多列最常见值列表[reference:41]示例CREATESTATISTICSstat_customer_city(dependencies)ONcustomer_id,cityFROMcustomers;ANALYZEcustomers;pg_settings所属PostgreSQL 系统视图用途查看和修改配置参数示例-- 查看成本相关参数SELECTname,setting,unit,contextFROMpg_settingsWHEREnameLIKE%cost%ORDERBYname;pg_stats所属PostgreSQL 系统视图用途查看列级别的统计信息[reference:42]示例SELECTschemaname,tablename,attname,null_frac,avg_width,n_distinct,most_common_vals,most_common_freqs,histogram_boundsFROMpg_statsWHEREtablenameproductsANDattnameprice;pg_stat_all_tables所属PostgreSQL 系统视图用途查看表的统计信息收集时间示例SELECTrelname,last_analyze,last_autoanalyze,seq_scan,seq_tup_read,idx_scan,idx_tup_fetchFROMpg_stat_all_tablesWHERErelnameorders;Demo 简单示例本 Demo 演示如何通过EXPLAIN ANALYZE分析查询性能并通过CREATE STATISTICS解决多列估算失真问题。环境准备-- 创建测试表CREATETABLEorders(idSERIALPRIMARYKEY,customer_idINTEGERNOTNULL,product_categoryVARCHAR(50)NOTNULL,order_dateDATENOTNULL,amountNUMERIC(10,2));-- 插入测试数据10000 行INSERTINTOorders(customer_id,product_category,order_date,amount)SELECT(random()*100)::INTEGER,(ARRAY[Electronics,Clothing,Books,Food,Toys])[(random()*41)::INTEGER],CURRENT_DATE-(random()*365)::INTEGER,(random()*1000)::NUMERIC(10,2)FROMgenerate_series(1,10000);-- 收集统计信息ANALYZEorders;问题查询-- 查询特定客户在特定类别的订单EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcustomer_id42ANDproduct_categoryElectronics;在未创建多元统计信息时优化器可能严重低估满足两个条件的行数。创建多元统计信息-- 创建函数依赖统计CREATESTATISTICSstat_orders_customer_category(dependencies)ONcustomer_id,product_categoryFROMorders;-- 重新收集统计信息ANALYZEorders;-- 再次执行查询EXPLAIN(ANALYZE,BUFFERS)SELECT*FROMordersWHEREcustomer_id42ANDproduct_categoryElectronics;运行说明在 PostgreSQL 10 数据库中执行上述 SQL对比创建多元统计信息前后的EXPLAIN ANALYZE输出中的rows估算值观察估算行数是否更接近实际行数技术点总结EXPLAIN ANALYZE输出执行计划的估算值与实际值多列条件在默认情况下存在估算失真CREATE STATISTICS创建多元统计信息可显著提升估算准确性pg_stats和pg_stat_all_tables用于查看统计信息状态项目难点与解决方案核心难点多列关联导致的估算失真PostgreSQL 优化器默认假设不同列的条件相互独立将各列选择率相乘得出总选择率。当列之间存在函数依赖时这种假设会导致行数严重低估进而引发错误的连接方法选择如 Nest Loop 替代 Hash Join最终导致查询性能急剧下降[reference:43]。解决方案使用CREATE STATISTICS创建多元统计信息具体包括函数依赖统计dependencies捕捉列之间的函数依赖关系多列 MCV 统计mcv记录多列组合的最常见值及其频率多列 NDISTINCT 统计ndistinct记录多列组合的不同值数量创建后执行ANALYZE使统计信息生效优化器将使用更精确的选择率进行估算[reference:44]。广度该问题影响所有涉及多列条件的查询包括多列WHERE条件多列GROUP BY多列JOIN条件多列DISTINCT深度解决该问题需要理解PostgreSQL 优化器的成本模型与统计信息机制选择率的计算原理与独立性假设的局限性多元统计信息的不同类型及其适用场景ANALYZE的执行时机与统计信息更新策略复杂度诊断复杂度需要通过EXPLAIN ANALYZE对比估算行数与实际行数识别估算失真节点实施复杂度CREATE STATISTICS语法简单但需要选择正确的统计类型dependencies、mcv或ndistinct维护复杂度统计信息会随数据变更而老化需确保autovacuum正常运作或定期执行ANALYZE官方文档EXPLAIN — PostgreSQL DocumentationUsing EXPLAIN — PostgreSQL DocumentationCREATE STATISTICS — PostgreSQL Documentationpg_stats — PostgreSQL DocumentationPlanner Cost Constants — PostgreSQL Documentation参考链接PEV2 — PostgreSQL Explain VisualizerPEV2 Online DemoDepesz Execution Plan AnalyzerMultivariate Statistics Examples — PostgreSQL Documentation总结本文系统梳理了 PostgreSQL 执行计划的核心概念与优化器的工作原理。执行计划作为树形结构遵循自底向上、从左往右的阅读规则。优化器基于成本模型选择执行路径其中seq_page_cost与random_page_cost是影响索引选择的关键参数尤其在 SSD/NVMe 时代需要进行针对性调优。统计信息是行数估算的数据基础pg_stats视图提供了histogram_bounds、most_common_vals等关键信息。针对多列关联导致的估算失真问题PostgreSQL 10 提供了CREATE STATISTICS多元统计信息功能可有效提升优化器估算精度。在实际调优中结合EXPLAIN ANALYZE与 PEV2 等可视化工具可快速定位高消耗节点与估算偏差实现高效的 SQL 性能优化。