一条SQL交到PostgreSQL手里从客户端发出去到拿到结果中间隔着的距离远比大多数人想象的要长。很多人日常写SQL、调SQL对“执行计划”这个词很熟但真被问到“这一条SQL到底是怎么一步步变成结果的”能完整讲清楚的人其实不多。我打算把整条链路拆开从连接协议、解析、重写、计划、执行到结果返回每个阶段都讲讲它在这一路上到底干了什么以及我们实际排查慢SQL时怎么利用这些知识少走弯路。这篇文章适合后端开发、DBA、以及所有想真正搞懂PostgreSQL SQL执行过程的人尤其是被慢SQL折磨过、想从“瞎试优化”升级到“按图索骥”的朋友。1. 连接与协议SQL从哪里进入 PostgreSQL想弄明白一条SQL在PostgreSQL里的完整旅程第一步不是看解析器而是看SQL是怎么进到数据库的。PostgreSQL是典型的进程模型一个客户端连接对应一个后端backend进程。客户端发起连接时主进程postmaster负责监听端口、完成鉴权然后fork出一个backend进程专门服务这个连接。这意味着什么意味着每条连接的执行状态、内存都是独立的一条SQL的执行不会直接污染另一条连接。但代价也很现实连接本身的创建开销很大所以生产环境才需要连接池而不是让应用频繁建连。客户端和backend之间走的是libpq协议。如果你只是用psql敲一条SQL回车走的是简单查询协议整条SQL文本打包成一个报文发给服务器服务器直接解析、执行、返回结果。但如果你用JDBC、psycopg、node-postgres这类驱动执行参数化查询走的是扩展查询协议分Parse、Bind、Execute三步。这一步很关键后面讲计划缓存的时候还会提到。具体来说Parse阶段客户端只发送带占位符的SQL文本服务器生成预处理语句prepared statementBind阶段把参数值绑定进去Execute阶段真正执行。好处是SQL文本只要解析一次参数可以反复绑定。pgbench压测里有个选项-M prepared就是强制走这条路径QPS能比simple模式高不少因为省掉了重复解析的开销。我们平时观察SQL执行最先看的是pg_stat_activity。这个视图能告诉你backend当前正在执行什么SQL、处于哪个等待事件。我排查线上问题第一步永远是看它因为执行过程卡在哪一阶段从wait_event大致能判断如果是ClientRead说明在等客户端发指令如果是DataFileRead多半在执行阶段读数据如果是Lock或transactionid那是锁等待。不要小看这一步很多慢SQL不是SQL本身慢而是卡在了协议或者等待上压根没走到执行器那一层。2. 解析阶段从文本到语法树解析阶段的目标很简单把SQL字符串变成一棵语法树。这一阶段不关心表存不存在、列对不对它只负责语法层面的合法性检查。PostgreSQL的解析器由两个工具生成词法分析用flex语法分析用bison。词法分析器扫SQL文本把关键字、标识符、数字、字符串、操作符拆成token比如SELECT、FROM、WHERE、id、123这些。语法分析器则按照gram.y里定义的文法把token序列组合成一棵RawParseTree也就是解析树。如果你写过解析器就知道这一步最容易踩坑的是操作符优先级。PostgreSQL对SQL标准做了很多扩展比如JSON的-和#你写错了层级报的错往往就是syntax error at or near。什么样的文本会被解析器拦下来比如漏了逗号、括号不匹配、字符串没闭合、给列名加了双引号又搞错了大小写。这些错误返回得很快因为根本到不了后边的分析阶段。我见过不少新手在PostgreSQL里建表用了大写或驼峰字段名结果每次查询都要用双引号把自己坑得很惨根子就在这一步的标识符大小写折叠规则上不加双引号的标识符统一转成小写加了双引号就严格区分大小写。这里顺便说一个安全点。很多人问SQL注入为什么参数化查询能防注入原因就在解析阶段。参数化查询把SQL文本和参数分开传输Parse阶段处理的是不带用户数据的文本参数在Bind阶段作为值绑定永远不会被当成SQL代码解析。所以只要你规范走参数化解析器根本不会把你的输入当成关键字去组装语法树。这不是PostgreSQL独有的而是整个数据库协议设计的通用安全边界。解析阶段还有一个细节它不区分表名和普通标识符所有标识符先按字符串处理。表名、列名是不是真的存在、类型对不对那是下一阶段的事。所以解析器报的“语法错误”和“列不存在”是完全两类问题前者秒退后者要等语义分析。3. 分析与重写语义检查和视图展开解析树只是一棵语法正确的树数据库还得搞清楚你到底在说谁。这一阶段叫analyze核心入口是parse_analyze函数把RawParseTree变成Query结构也就是查询树。分析阶段要干的事很多解析表名对应的实表、解析列名、判断类型、查找操作符和函数。比如你写WHERE id 123如果id是integer列分析器会找到int4的等号操作符并把字符串常量转成整数。如果你写了一个不存在的列错误也是在这一阶段报出来的column xxx does not exist。权限检查也从这里开始PostgreSQL在分析阶段就要校验你有没有访问对应表、列的权限没权限直接报permission denied。接下来是重写阶段。PostgreSQL有一个规则系统底层是pg_rewrite系统表。最常见的规则就是视图VIEW。你创建视图时系统会记录一条规则查询视图时把视图名替换成视图定义里的SELECT子查询。所以执行时根本不存在“扫视图”这回事——视图就是一个宏会被展开成底层表的查询。重写阶段还会做一些子查询规范化。比如你写的IN子查询或者EXISTS会在后续计划阶段被改写成半连接或反连接。但有些子查询不会被提升比如带聚合、带LIMIT、带非等值关联条件的子查询它们会保留为SubPlan执行时每行外层数据都可能去执行一次子计划。这在EXPLAIN里能看到表现为SubPlan节点或InitPlan。我调试SQL时如果看到一条IN子查询性能差经常会先看是不是部分子查询没被提升导致它被当成参数化子计划反复执行。重写阶段有个经典坑嵌套视图膨胀。视图套视图每套一层就多一层替换最后可能展开出一个很大的查询树。优化器处理大查询树会吃力而且统计信息在多层投影下容易失真。这不是说不能用视图而是别把视图嵌套得太深超过三四层就要警惕了。另外物化视图不走这个机制它是一个真实的物理存储对象查询它时直接扫描不需要展开规则。4. 计划阶段优化器如何选路计划阶段是整个SQL执行过程里最复杂的部分也是慢SQL优化的主战场。简单说优化器输入查询树输出一棵执行计划树。难点在于同样一个查询扫描方式、连接方式、连接顺序有无数种组合优化器要挑一个代价最小的。PostgreSQL用的是基于代价的优化CBO。每条路径都有个估计代价单位是抽象的成本单位约定一次顺序页读取约等于1.0。随机页读取成本由random_page_cost控制默认是4它反映的是机械硬盘时代随机IO和顺序IO的巨大差距。你在SSD上跑业务这个参数其实可以调低一点比如2甚至1.1否则优化器会过于偏好顺序扫描。代价估算依赖统计信息pg_class里的relpages页面数和reltuples行数以及pg_statistic里的列分布、直方图、常用值MCV、NULL比例等。扫描方式的选择也是有规律可循的。小表或者要取大比例行时顺序扫描通常最快要取少比例行且有可用索引时走索引扫描如果条件命中多个索引或者一个索引返回比例较高可能走Bitmap扫描。Bitmap Heap Scan的原理是先用索引构建一个行位置位图再按物理顺序回表取数据把大量随机IO变成近似顺序IO。这个机制理解透了就明白为什么位图扫描在“选了索引但选择性又不高”的场景里表现特别好。连接方式有三种Nested Loop、Hash Join、Merge Join。Nested Loop适合外表小、内表有索引并且能参数化匹配的场景比如两个表各几百行外层每行去内层索引里查一下Hash Join适合等值连接且数据量中等以上优化器会先选择统计上行数较少的表做build端建哈希表再用大表去probeMerge Join适合两边已经有序或者连接列上刚好有排序条件可以利用索引顺序直接归并。优化器选择哪种方式完全看代价估算Nested Loop的代价约等于外表行数乘以内表单次探测成本Hash Join的代价约等于建哈希表成本加探测成本Merge Join则要考虑排序成本。连接顺序的选择是个组合优化问题。PostgreSQL默认用动态规划穷举所有连接顺序但当FROM涉及的表太多时默认阈值geqo_threshold是12超过12个rels就会切换成遗传算法GEQO不再保证最优解。所以为什么有人一遇到十几个表join就慢一部分原因是优化器根本没去穷举只是快速找了个过得去的方案。遇到这种场景你得手动拆查询、调整join顺序或者适当提高geqo阈值让优化器多算一会儿。统计信息的准确性几乎决定一切。有一句圈内老话优化器是“信统计信息的”统计信息不准计划再聪明也是盲人摸象。最常见的问题是自动分析没跟上表的行数变化巨大但reltuples还是老黄历导致优化器严重低估某个表返回的行数进而选了错误的连接方式。解决思路是及时ANALYZE对关键表可以调低autovacuum的analyze触发阈值。遇到多列条件相关性强的查询还可以建扩展统计信息CREATE STATISTICS让优化器看到列之间的依赖关系而不是天真地假设各列独立。计划缓存也是一个很有意思的话题。扩展协议下的prepared statement第一次执行会用custom plan专门针对当前参数值定制计划。但如果参数多种多样系统在5次custom plan之后可能生成一个generic plan也就是对所有参数通用的计划。这本来是为了省计划生成开销但通用计划一旦基于某些普通参数选定遇到数据偏斜时可能远不是最优。遇到这种case可以用plan_cache_mode强制走custom plan或者改用SQL内联参数。PL/pgSQL函数内部也有类似的计划缓存机制这也是为什么有人发现函数里同样的SQL数据分布变了性能却不恢复。并行查询也是计划阶段的决策之一。优化器会评估这个查询值不值得并行表够大吗min_parallel_table_scan_size、并行setup成本高不高、最多能开几个worker。并行计划会把扫描、join、聚合拆给多个worker最后leader汇总。但我的经验是并行不是白送的小查询开并行反而因为调度和通信开销拖慢所以千万别盲目调大并行度尤其是OLTP场景几十毫秒的小查询开了并行反而可能变成几百毫秒。5. 执行阶段计划变成真实行集执行阶段就是把计划树跑起来逐节点生成和传递数据。PostgreSQL的执行器是经典的火山模型Volcano Model每个节点实现ExecInitNode、ExecProcNode、ExecEndNode三个接口上层节点每次调用下层节点的ExecProcNode拿一行tuple处理完再往上抛。一行一行地流过整棵计划树直到最上层把结果发送给客户端。这种模型简单、灵活但行式迭代也有代价每个节点都有函数调用开销而且一次只处理一行CPU利用率不高。PostgreSQL为此做了不少优化比如表达式计算通过ExprContext批量处理还有JIT编译PG11引入把表达式翻译成机器码减少解释执行开销。新版本还在做批次执行tuple batch的尝试目的是减少函数调用频率。理解了火山模型你就明白为什么EXPLAIN里每一层都有startup cost和total cost它对应着这层节点建立起来要花多少成本、输出第一行前要花多少。各类执行节点的运行方式是排查性能问题的核心知识储备SeqScan从头到尾扫heap逐块读buffer过滤条件的谓词在每个tuple上计算。IndexScan从索引根节点出发按B树查找定位到叶子再沿链表返回匹配的索引项然后根据可见性拿到heap tuple。注意索引扫描不一定回表如果查询列全在索引里就变成Index Only Scan当然还要拿visibility map确认可见性。BitmapIndexScanBitmapHeapScan一个构建位图一个读取位图并回表。NestedLoop外层循环每一行内层用参数化路径去查索引。EXPLAIN里的loops一项往往等于外层行数所以外层越大越危险。HashJoin先对build端建哈希表然后逐行probe。如果build端数据超过work_mem会分batch落盘EXPLAIN里能看到Hash Batches: n这意味着哈希join被溢出了速度暴跌。Sort内存里排序超过work_mem就写临时文件。EXPLAIN ANALYZE里Sort Method: external merge Disk就是溢出证据。Agg如果是GROUP BY且输入顺序恰好有序可以用GroupAggregate避免排序否则走HashAggregate内存不够同样会hash batch。Limit这个节点有短路语义只要上层要的行数够了底下节点直接终止执行不会白干活。执行阶段的内存和IO管理非常关键。shared_buffers是共享缓冲池所有backend读数据先到buffer里命中就是shared hit没命中就去磁盘读然后进buffer。EXPLAIN ANALYZE的Buffers输出能告诉你每个节点读了多少块、命中了多少。work_mem则是每个backend私有的排序、哈希内存注意它是“每个操作”计算的不是每个连接一份那么简单。一个大哈希join加两个排序work_mem按3倍消耗是常有的事这也是为什么不能盲目把work_mem调大一个连接多个操作同时进行时内存消耗是倍增的。结果返回阶段也要说一句。执行器把结果tuple交给目标接收器DestReceiver最终打包成客户端能读的格式发送。psql默认文本格式JDBC驱动通常会请求二进制格式传输效率高一些。游标是一次取一批这也会影响执行器的物化行为比如某些节点需要materialize才能支持重新扫描。事务和快照在这里也牵涉进来。一条SQL在事务里执行时使用的快照在语句开始前就确立了MVCC保证它看不到未提交的数据。遇到行锁冲突执行器会等锁死锁则由专门的检测进程处理。这时候pg_stat_activity里能看到等锁等待事件。一条慢SQL到底是在执行SQL本身还是卡在锁上看这个就能区分。6. 排查实录从执行计划读懂一条慢SQL前面讲了一堆理论实际工作中怎么用我建议所有优化工作都从EXPLAIN ANALYZE开始而且一定要开BUFFERS选项EXPLAIN (ANALYZE, BUFFERS) SELECT ...。不加BUFFERS你只能看到时间看不到IO容易误判是CPU问题还是磁盘问题。分享一个真实遇到过的案例。某报表查询一个小表A几千行join一个大表B几千万行条件是A.id B.a_id。执行计划显示用的是Nested Loop每行A都去B上走索引loops一项是几千实际跑了快两分钟。为什么优化器选了这个看着很笨的方案因为它严重低估了A返回的行数——A表最近一周新插入了大量数据但autovacuum的analyze还没跑优化器还以为A只有几百行。解决办法很简单对A表执行一次手动ANALYZE执行计划立刻变成Hash Join耗时从两分钟降到几百毫秒。这不是玄学是统计信息更新的经典案例。另一个常见问题是work_mem不足。我见过一个SQL排序加哈希都特别吃内存EXPLAIN ANALYZE显示Sort Method: external merge Disk和Hash Batches: 4。解决办法是给这个查询单独调大work_mem因为全局调大风险很高每个连接每个操作都可能吃到那么大内存几十个连接同时跑起来内存池很容易被撑爆。你可以这样在事务或会话开头SET LOCAL work_mem 256MB只对当前会话生效。注意是SET LOCAL不是SET否则会污染整个连接后续的所有查询。索引失效的排查也经常遇到。最典型的坑是隐式类型转换。比如某个字符列是text或varchar查询条件写成WHERE col 12345因为12345是integerPostgreSQL会因为类型不匹配而尝试把col转成integer或者反过来把常量转成text。如果转换是把列包了一层函数索引就失效了。解决办法查询条件写成 12345或者给表达式建函数索引。另一个坑是前缀模糊查询WHERE col LIKE %abc%普通B树索引用不上可以考虑pg_trgm的gin索引。还有一个容易被忽略的问题排序规则如果索引和查询的collation不一致也可能导致索引不可用。参数化查询的劣化也要提一下。线上有个接口用了prepared statement平时一切正常遇到一个数据量特别大的参数就慢。查执行计划发现走的是generic plan针对通用情况选了顺序扫描大表。处理方式之一是在该场景下把plan_cache_mode设为force_custom_plan或者把SQL改成不用prepared而用普通文本。但这里要强调参数化是防注入的安全底线优先考虑保留prepared、通过改写SQL或调参解决而不是放弃参数化。安全不能为了性能让步。排查慢SQL的通用套路我一般按这个顺序来先用pg_stat_statements或auto_explain抓出慢SQL。用EXPLAIN (ANALYZE, BUFFERS)看计划重点对比actual rows和rows的差距。如果rows严重低或高估先ANALYZE再看统计信息是否过期必要时建扩展统计信息。如果plan看着正常但就是慢看Buffers里的shared hit和read以及temp files和writes判断是IO问题还是内存问题。最后才考虑加索引或改写SQL。这个顺序很重要。我见过太多人一上来就加索引结果发现是统计信息问题索引根本没用也见过一些人不停地调优化器参数实际是SQL写法和索引选择的问题。先诊断再动手才能少做无用功。6.1 常见问题速查症状可能环节优先检查项执行计划里rows严重偏离actual rows计划阶段统计信息表是否长时间未ANALYZE统计信息是否过期相同SQL时快时慢计划缓存是否用了prepared statement走generic plan哈希join或排序慢且临时文件多执行阶段内存work_mem是否过小是否出现Hash Batches或external merge Disk有索引但没用上计划阶段/类型解析是否隐式类型转换条件是否前导通配符锁等待卡住锁/事务pg_stat_activity的wait_event阻塞关系并行反而更慢计划阶段并行评估表太小或并行度设置过高适当降低并行参数6.2 两个我常用的辅助工具auto_explain是排查线上慢SQL的神器。在postgresql.conf里配置好session_preload_libraries auto_explain加上auto_explain.log_min_duration 500ms所有超过500ms的SQL都会连同执行计划一起写进日志。你不需要提前知道哪条SQL慢日志会帮你筛出来。配合log_line_prefix里的应用名能快速定位到具体业务。pg_stat_statements则是全局维度的统计视图能看到每条SQL的平均执行时间、调用次数、shared hit和read块数。它特别适合找高频慢查询也就是“每次都不算特别慢但一秒执行千次、合计把CPU吃满”的那种。这种SQL光看top耗时是抓不到的必须看总耗时排序。6.3 手工改写SQL的时机优化器不是万能的但也不是随便改两下就比它强。我个人的经验是只有当你从执行计划里明确看到了问题才值得去改写SQL。比如子查询没有被提升、外层和子层各扫了一遍大表这时候把相关子查询改成JOIN或者把NOT EXISTS改成LEFT JOIN加过滤条件收益通常非常明显。又比如你在视图上查了十几列但实际上只需要3列把视图外层再做一次投影裁剪能减少不少计算量。反过来如果你只是觉得“这个查询比较复杂我手动拆成两个查询吧”那大概率不会比优化器的方案更好因为拆开之后临时数据可能又引入了新的统计信息失真。6.4 最后提醒几个细节执行过程里容易被忽视的几个点我最后集中说一下。第一EXPLAIN不带ANALYZE时只是估算千万不要拿它去做性能对比要看就看EXPLAIN ANALYZE。第二BUFFERS选项看不出来是顺序IO还是随机IO配合track_io_timing on能看到实际IO耗时。第三rows估算偏差在10倍以内都算正常超过百倍才需要警惕不用对每一处偏差都如临大敌。第四函数内的SQL优化难度比裸SQL高不少因为计划缓存的存在函数第一次调用时的数据分布可能决定后面所有调用的执行计划测试函数性能时一定要用有代表性的数据去预热。把PostgreSQL的SQL执行过程从头到尾理解一遍之后你再遇到慢SQL心态会完全不一样。从前是瞎试现在是按阶段定位是卡在解析、计划还是执行是优化器被统计信息骗了还是work_mem不够导致溢出每一条慢SQL背后几乎都能对应到执行链路里的某个环节。这篇文章讲的是原理但你真正上手排查几回之后就会发现这些原理全都变成了你手里的工具。我个人在实际操作中的体会是多数慢SQL问题根本轮不到调内核参数先把统计信息弄新鲜、把work_mem从默认的4MB提上去、把SQL的隐式转换修掉大部分坑就填平了。希望这篇执行全过程能帮你把这条链路彻底串起来排查问题的时候少点盲区少熬几个夜的功夫。