PostgreSQL 明明有索引却选了 Nested Loop：从行数误判修正执行计划
同一条订单查询小参数时几十毫秒大参数时却长时间占用数据库EXPLAIN显示优化器预计返回 12 行实际执行产生了十几万行随后 Nested Loop 的内表被反复扫描。问题不在于 PostgreSQL “不认识索引”而在于它基于错误的行数估计选错了连接路径。本文用一个country与currency强相关的例子说明如何找到第一次估算分叉。文中的 SQL 是可执行的诊断样例但这里没有连接你的数据库因此不会把示例结果写成实测结论。先复现两个相关条件被当成彼此独立假设订单表中中国区订单几乎都以 CNY 结算CREATETABLEorders(idbigintGENERATED ALWAYSASIDENTITYPRIMARYKEY,customer_idbigintNOTNULL,countrytextNOTNULL,currencytextNOTNULL,created_at timestamptzNOTNULL);CREATEINDEXidx_orders_customer_createdONorders(customer_id,created_atDESC);只收集单列统计信息时规划器可能分别估计country CN与currency CNY的选择率再把两者相乘。真实数据中的相关性没有进入模型中间结果便会被严重低估。在测试库中用下面的命令保留执行证据EXPLAIN(ANALYZE,BUFFERS,SETTINGS,FORMATTEXT)SELECTc.id,o.id,o.created_atFROMcustomersAScJOINordersASoONo.customer_idc.idWHEREo.countryCNANDo.currencyCNYANDo.created_atnow()-interval30 days;ANALYZE会真的执行查询不要把修改型 SQL 原样放到生产环境。排查时从计划树内层向外找第一个rows与actual rows明显分叉的节点并把loops一起看最外层耗时只是结果第一次误判才是线索。不要先禁用连接算法先检查规划器掌握了什么先查看自动分析时间和列分布SELECTrelname,last_analyze,last_autoanalyze,n_live_tupFROMpg_stat_user_tablesWHERErelnameIN(orders,customers);SELECTattname,n_distinct,most_common_vals,most_common_freqsFROMpg_statsWHEREschemanamepublicANDtablenameordersANDattnameIN(country,currency,customer_id);成功的诊断不是“强制走了 Hash Join”而是能回答三个问题统计信息是否过期、目标值是否在高频值列表中、多个过滤列是否存在业务相关性。若只是批量导入后统计信息陈旧先执行ANALYZE orders若误差稳定来自相关列再考虑扩展统计CREATESTATISTICSst_orders_country_currency(dependencies,mcv)ONcountry,currencyFROMorders;ANALYZEorders;dependencies描述列依赖mcv保存常见组合。它们帮助过滤条件估算但不会自动替代缺失的连接索引也不能修复写错的 Join 条件。做一个反事实实验而不是永久关闭 Nested Loop在事务内临时改变规划器开关可以验证“另一类计划是否值得继续调查”BEGIN;SETLOCALenable_nestloopoff;EXPLAIN(ANALYZE,BUFFERS)SELECT/* 同一条查询参数保持一致 */;ROLLBACK;这只是反事实实验。若另一计划更合适应继续修正统计信息、SQL 或索引而不是在全局配置中禁用 Nested Loop。小结果集驱动索引查找时Nested Loop 往往正是正确选择。还要防止只验证一组参数。把典型小客户、普通客户和头部客户的参数各选一组分别保存计划。预备语句使用通用计划时参数分布差异尤其容易被平均值掩盖。验收修复关注估算误差而非计划节点名称可以把计划保存为 JSON再检查目标节点的估算倍率psql$DATABASE_URL-X-vON_ERROR_STOP1-Atc\EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT c.id, o.id FROM customers c JOIN orders o ON o.customer_idc.id WHERE o.countryCN AND o.currencyCNY;\plan.json python-mjson.tool plan.json/dev/nulltest-splan.json上述命令的可验证成功条件是psql退出码为 0、plan.json非空且能被 JSON 解析。性能层面的验收还需比较修改前后相同数据快照、相同参数和相同缓存条件下的计划重点记录首次分叉节点的估算/实际行数倍率、缓冲区读取和总执行时间。失败条件包括估算误差没有缩小、只对单一参数改善或其他高频查询出现回退。优化器调优的目标不是让 SQL 永远使用某个索引而是让成本模型获得足够准确的输入。先定位第一次估错再决定更新统计、添加扩展统计、调整索引还是改写查询通常比直接改全局成本参数更可控。
)
)
