1、POSTGRESQL执行计划详解分析SQL的基础何敏，曾就职于成都文武、人大金仓，参与开发PG高可用系统、RDS、数据库接口。精通数据库迁移、高可用、系统方案设计，有丰富的开发和运维经验。PG中文社区首届 PG MVP中国首期PG ACE 伙伴中国POSTGRESQL分会官方认证讲师盘古云课堂高级讲师PostgreSQL在执行任何查询时，都会先经过语法、语义解析，生成查询表达式树；然后根据规则系统对查询树进行转换，视图也在这里展开，最终生成逻辑查询树；最后根据系统统计信息、物理存储的情况对查询进行优化，生成物理查询计划；最后根据查询计划进行执行，最终得到结果。因此，选择正确的查询结构和数据属性

2、的规划，对执行效率至关重要。我们可以通过explain命令来查看执行计划，进而对不合理的地方进行调整，提高SQL的执行效率。解析器分析器重写器计划器执行器语法解析：语法解析树语义分析：查询树重写：查询树执行计划树1.执行预处理2.找出代价最小的路径3.创建计划树初初识识执执行行计计划划初初识识执执行行计计划划通过在SQL语句前面加上explain操作，就可以获取到该SQL的执行计划，该SQL并没有实际执行。输出的执行计划有如下特点：1.查询规划是以规划为节点的树形结构。树的最底节点是扫描节点：他返回表中的原数据行。2.不同的表有不同的扫描节点类型：顺序扫描，索引扫描和位图索引扫描。3.也有非表

3、列源，如VALUES子句并设置FROM返回，他们有自己的扫描类型。4.如果查询需要关联，聚合，排序或其他操作，会在扫描节点之上增加节点执行这些操作。5.EXPLAIN的输出是每个树节点显示一行，内容是基本节点类型和执行节点的消耗评估。可能会出现同级别的节点，从汇总行节点缩进显示节点的其他属性。第一行（最上节点的汇总行）是评估执行计划的总消耗，这个值越小越好。初初识识执执行行计计划划因为这个查询没有WHERE子句，所以必须扫描表中的所有行，所以规划器选择使用简单的顺序扫描规划。括号中的数字从左到右依次是：1.评估开始消耗：这是可以开始输出前的时间，比如排序节点的排序的时间。2.评估总消耗：假设查

4、询从执行到结束的时间。有时父节点可能停止这个过程，比如LIMIT子句。3.评估查询节点的输出行数，假设该节点执行结束。4.评估查询节点的输出行的平均字节数。初初识识执执行行计计划划需要知道的是：上级节点的消耗包括其子节点的消耗。这个消耗值只反映规划器关心的内容，一般这个消耗不包括将数据传输到客户端的时间。评估的行数不是执行和扫描查询节点的数量，而是节点返回的数量。它通常会少于扫描数量，因为有WHERE条件会过滤掉一些数据。理想情况顶级行数评估近似于实际返回的数量。这个消耗的计算依赖于规划器的设置参数，这里的例子都是在默认参数下运行。cost描述一个SQL执行的代价是多少，而不是具体的时间。下面

5、是默认情况下，对数据操作的消耗评估基础：初初识识执执行行计计划划回到刚才的例子，表test有10000条数据分布在94个磁盘页，评估时间是（磁盘页*seq_page_cost）+（扫描行*cpu_tuple_cost）。默认seq_page_cost是1.0，cpu_tuple_cost是0.01，所以评估值是(94*1.0)+(10000*0.01)=194。什么时候更新的pg_class、pg_stat_user_tables等统计信息初初识识执执行行计计划划查询节点增加了“filter”条件。这意味着查询节点为扫描的每一行数据增加条件检查，只输入符合条件数据。评估的输出记录数因为wher

6、e子句变少了，但是扫描的数据还是10000条，所以消耗没有减少，反而增加了一点CPU的计算时间。这个查询实际输出的记录数是1000，但是评估是个近似值，多次运行可能略有差别，这中情况可以通过ANALYZE命令改善。几几种种扫扫描描方方式式全全表表扫扫描描：Seq Scan全表扫描，当数据表中没有索引，或者满足条件的数据集较大，索引扫描的成本高于全表扫描，这时规划器会选择使用全表扫描。至于全表扫描的过程和索引扫描的过程在这里不详细说，后续可以再开一个主题。几几种种扫扫描描方方式式索索引引扫扫描描：index scan如果查询的列创建有索引，则直接扫描索引，不再进行全表扫描，耗费时间小于顺序扫描。

7、多了筛选条件后，会打开每条记录，进行筛选记录，花费时间变多了。但是，将筛选条件放到扫描里面是有好处的，尤其是在多表join时，构造自然选择的块有很大的区别 几几种种扫扫描描方方式式全全索索引引扫扫描描：index only scan当查询的条件都在索引中，也会走该扫描方式，不会读取表文件。几几种种扫扫描描方方式式位图扫描：Bitmap Index Scan 位图扫描也是一种走索引的方式，方法是扫描索引，把满足条件的行或者块在内存中建一个位图，扫描完索引后，再跟进位图中记录的指针到表的数据文件读取相应的数据。在or、and、in子句和有多个条件都可以同时走不同的索引时，都可能走Bitmap In

8、dex Scan：几几种种扫扫描描方方式式位图扫描：Bitmap Index Scan Bitmap Heap Scan的启动时间就是两个子Bitmap Index Scan的总和，可以看出在进行组合时花费了大量时间。Bitmap Index Scan和Index Scan扫描的区别很明显：Index scan:输出的是tuple，它先扫描索引块，然后得到rowid扫描数据块得到目标记录。一次只读一条索引项，那么一个 PAGE面有可能被多次访问.Bitmap index scan;输出的是索引条目，并不是行的数据，输出索引条目后，交给上一个节点 bitmap heap scan(之间可能将索引

9、条目根据物理排列顺序进行排序)。一次性将满足条件的索引项全部取出，然后交给bitmap heap scan节点，并在内存中进行排序,根据取出的索引项访问表数据。几几种种扫扫描描方方式式limit对执行计划的影响 这条查询的where条件和上面的一样，只是增加了limit，所以不是所有数据都需要返回，规划器在发现limit后改变了规划。虽然在索引扫描节点总消耗和返回记录数是244，但查询是从上往下拉取数据的，当扫描到满足要求的2行结果后，达到了Limit 2 取两条的要求，就直接返回了，预期时间消耗是26，所以总时间消耗是26。几种连接方式 Hash Join 这个计划是使用hash join的

10、方式来进行表连接的，首先确定两个表的大小，使用小表建立hash map，然后扫描大表，比较hash值，最终获取查询结果。几种连接方式 连连接接方方式式说说明明执执行行步步骤骤适适用用场场景景时时间间消消耗耗hash join散列连接是做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较

11、大的临时段从而尽量提高I/O 的性能。将两个表中较小的一个在内存中构造一个HASH表（对JOIN KEY），扫描另一个表，同样对JOIN KEY进行HASH后探测是否可以JOIN。适用于记录集比较大的情况。需要注意的是：如果HASH表太大，无法一次构造在内存中，则分成若干个partition，写入磁盘的temporary segment，则会多一个写的代价，会降低效率 两个大表关联查询cost=(outer access cost*#of hash partitions)+inner access cost几种连接方式 Nested Loop Join 这个规划中有一个内连接的节点，它有两个子

12、节点。节点摘要行的缩进反映了规划树的结构。最外层是一个连接节点，子节点是一个Seq Scan扫描。外部节点为t1.info=3756da242bee7967edcd3041769f8f96的结果。接下来为每一个从外部节点得到的记录运行内部查询节点（t.id 10）。外部节点的消耗加上循环内部节点的消耗（2.26+1*8.44）再加一点CPU时间就得到规划的总消耗10.82。几种连接方式 连连接接方方式式说说明明执执行行步步骤骤适适用用场场景景时时间间消消耗耗nested loop join对于被连接的数据子集较小的情况，嵌套循环连接是个较好的选择。在嵌套循环中，内表被外表驱动，外表返回的每一行

13、都要在内表中检索找到与它匹配的行，因此整个查询返回的结果集不能太大（大于1 万不适合），要把返回子集较小表的作为外表（默认外表是驱动表），而且在内表的连接字段上一定要有索引。确定一个驱动表(outer table)，另一个表为inner table，驱动表中的每一行与inner表中的相应记录JOIN。类似一个嵌套的循环。Nested loop一般用在连接的表中有索引，并且索引选择性较好的时候。适用于驱动表的记录集比较小（批量 copy从SQL入手：CTE减少嵌套减少子查询物化视图、临时表Exits使用拆分SQL改写SQL类型转换失真 从从数数据据库库参参数数入入手手：精确统计信息干涉执行计划调整性能参数pg_hint_plan其其他他：连接池操作系统参数硬件性能THANKS谢谢观看