PostgreSql的Explain命令详解
前端之家收集整理的这篇文章主要介绍了
PostgreSql的Explain命令详解，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
http://toplchx.iteye.com/blog/2091860
使用EXPLAIN
Postgre
sql为每个收到的
查询设计一个
查询规划。选择正确的匹配
查询结构和数据
属性的规划对执行效率是至关重要要的，所以系统包含一个复杂的规划器来试图选择好的规划。你可以使用EXPLAIN命令查看
查询规划器创建的任何
查询。阅读
查询规划是一门艺术，需要掌握一定的经验，本节试图涵盖一些基础知识。
以下的例子来自Postgre
sql 9.3开发版。
EXPLAIN基础
查询规划是以规划为节点的树形结构。树的最底节点是扫描节点：他返回表中的原数据行。
不同的表有不同的扫描节点类型：顺序扫描，索引扫描和位图索引扫描。
也有非表列源，如VALUES子句并设置FROM返回，他们有自己的扫描类型。
如果
查询需要关联，聚合，排序或其他操作，会在扫描节点之上
增加节点执行这些操作。通常有不只一种可能的方式做这些操作，所以可能出现不同的节点类型。
EXPLAIN的
输出是每个树节点
显示一行，
内容是基本节点类型和执行节点的消耗评估。可能会楚翔其他行，从汇总行节点缩进
显示节点的其他
属性。第一行（最上节点的汇总行）是评估执行计划的总消耗，这个值越小越好。
下面是一个简单的例子：
- EXPLAINSELECT*FROMtenk1;
-
- QUERYPLAN
-
- SeqScanontenk1(cost=0.00..458.00rows=10000width=244)
因为这个
查询没有WHERE子句，所以必须扫描表中的所有行，所以规划器选择使用简单的顺序扫描规划。括号中的数字从左到右依次是：
- 评估开始消耗。这是可以开始输出前的时间，比如排序节点的排序的时间。
- 评估总消耗。假设查询从执行到结束的时间。有时父节点可能停止这个过程，比如LIMIT子句。
- 评估查询节点的输出行数，假设该节点执行结束。
- 评估查询节点的输出行的平均字节数。
这个消耗的计算依赖于规划器的设置参数，这里的例子都是在默认参数下运行。
需要知道的是：上级节点的消耗
包括其子节点的消耗。这个消耗值只反映规划器关心的
内容，一般这个消耗不
包括将数据传输到客户端的时间。
评估的行数不是执行和扫描
查询节点的
数量，而是节点返回的
数量。它通常会少于扫描
数量，因为有WHERE条件会过滤掉一些数据。理想情况顶级行数评估近似于实际返回的
数量
回到刚才的例子，表tenk1有10000条数据分布在358个磁盘页，评估时间是（磁盘页*seq_page_cost）+（扫描行*
cpu_tuple_cost）。默认seq_page_cost是1.0，
cpu_tuple_cost是0.01，所以评估值是(358 * 1.0) + (10000 * 0.01) = 458
FROMtenk1WHEREunique1<7000;
------------------------------------------------------------
ontenk1(cost=0.00..483.00rows=7001width=244)
Filter:(unique1<7000)
查询节点
增加了“filter”条件。这意味着
查询节点为扫描的每一行数据
增加条件检查，只输入符合条件数据。评估的
输出记录数因为where子句变少了，但是扫描的数据还是10000条，所以消耗没有减少，反而
增加了一点cup的计算时间。
这个
查询实际
输出的记录数是7000，但是评估是个近似值，多次运行可能略有差别，这中情况可以通过ANALYZE命令改善。
WHEREunique1<100;
------------------------------------------------------------------------------
BitmapHeapScanontenk1(cost=5.07..229.20rows=101width=244)
RecheckCond:(unique1<100)
->BitmapIndexScanontenk1_unique1(cost=0.00..5.04rows=101width=0)
IndexCond:(unique1<100)
查询规划器决定使用两步规划：首先子
查询节点查看索引找到符合条件的记录索引，然后外层
查询节点将这些记录从表中
提取出来。分别
提取数据的成本要高于顺序读取，但因为不需要读取所有磁盘页，所以总消耗比较小。（其中Bitmap是系统排序的一种机制）
WHEREunique1<100ANDstringu1='xxx';
ontenk1(cost=5.04..229.43rows=1width=244)
Filter:(stringu1='xxx'::name)
IndexCond:(unique1<100)
增加的条件stringu1='xxx'减少了
输出记录数的评估，但没有减少时间消耗，应为系统还是要
查询相同
数量的记录。请注意stringu1不是索引条件。
如果在不同的字段上有独立的索引，规划器可能选择使用AND或者OR组合索引：
ANDunique2>9000;
-------------------------------------------------------------------------------------
ontenk1(cost=25.08..60.21rows=10width=244)
RecheckCond:((unique1<100)AND(unique2>9000))
->BitmapAnd(cost=25.08..25.08rows=10width=0)
IndexCond:(unique1<100)
ontenk1_unique2(cost=0.00..19.78rows=999width=0)
IndexCond:(unique2>9000)
这个
查询条件的两个字段都有索引，索引不需要filre。
下面我们来看看LIMIT的影响：
ANDunique2>9000LIMIT2;
Limit(cost=0.29..14.48rows=2width=244)
->IndexScanusingtenk1_unique2ontenk1(cost=0.29..71.27IndexCond:(unique2>9000)
Filter:(unique1<100)
这条
查询的where条件和上面的一样，只是
增加了LIMIT，所以不是所有数据都需要返回，规划器改变了规划。在索引扫描节点总消耗和返回记录数是运行玩
查询之后的数值，但Limit节点预期时间消耗是15，所以总时间消耗是15.
增加LIMIT会使启动时间小幅
增加（0.25->0.29）。
来看一下通过索引字段的表连接：
SELECT*
FROMtenk1t1,tenk2t2
WHEREt1.unique1<10ANDt1.unique2=t2.unique2;
--------------------------------------------------------------------------------------
NestedLoop(cost=4.65..118.62rows=10width=488)
->BitmapHeapScanontenk1t1(cost=4.36..39.47 RecheckCond:(unique1<10)
ontenk1_unique1(cost=0.00..4.36IndexCond:(unique1<10)
IndexScanusingtenk2_unique2ontenk2t2(cost=0.29..7.91IndexCond:(unique2=t1.unique2)
这个规划中有一个内连接的节点，它有两个子节点。节点
摘要行的缩进反映了规划树的结构。最外层是一个连接节点，子节点是一个Bitmap扫描。外部节点位图扫描的消耗和记录数如同我们使用SELECT...WHERE unique1 < 10，因为这时t1.unique2 = t2.unique2还不相关。接下来为每一个从外部节点得到的记录运行内部
查询节点。这里外部节点得到的数据的t1.unique2值是可用的，所以我们得到的计划和SELECT...WHEREt2.unique2=constant的情况类似。（考虑到缓存的因素评估的消耗可能要小一些）
外部节点的消耗
加上循环内部节点的消耗（39.47+10*7.91）再加一点
cpu时间就得到规划的总消耗。
再看一个例子：
ANDt2.unique2<10ANDt1.hundred<t2.hundred;
---------------------------------------------------------------------------------------------
NestedLoop(cost=4.65..49.46rows=33width=488)
JoinFilter:(t1.hundred<t2.hundred)
->Materialize(cost=0.29..8.51ontenk2t2(cost=0.29..8.46IndexCond:(unique2<10)
条件t1.hundred<t2.hundred不在tenk2_unique2索引中，所以这个条件出现在连接节点中。这将减少连接节点的评估
输出记录数，但不会改变子节点的扫描数。
注意这次规划器选择使用Meaterialize节点，将条件加入内部节点，这以为着内部节点的索引扫描只做一次，即使嵌套循环需要读取这些数据10次，Meterialize节点将数据保存在内存中，每次循环都从内存中读取数据。
WHEREt1.unique1<100------------------------------------------------------------------------------------------
HashJoin(cost=230.47..713.98rows=101width=488)
HashCond:(t2.unique2=t1.unique2)
->SeqScanontenk2t2(cost=0.00..445.00rows=10000width=244)
->Hash(cost=229.20..229.20ontenk1t1(cost=5.07..229.20IndexCond:(unique1<100)
这里规划器选择使用hash join，将一个表的数据存入内存中的哈希表，然后扫描另一个表并和哈希表中的每一条数据进行匹配。
注意缩进反应的规划结构。在tenk1表上的bitmap扫描结果作为Hash节点的输入建立哈希表。然后Hash Join节点读取外层子节点的数据，再循环检索哈希表的数据。
另一个可能的连接类型是merge join：
Highlighter" id="" style="font-family:Monaco,onekt2
MergeJoin(cost=198.11..268.19 MergeCond:(t1.unique2=t2.unique2)
ontenk1t1(cost=0.29..656.28 Filter:(unique1<100)
->Sort(cost=197.83..200.33rows=1000width=244)
SortKey:t2.unique2
ononekt2(cost=0.00..148.00rows=1000width=244)
Merge Join需要已经排序的输入数据。在这个规划中按正确顺序索引扫描tenk1的数据，但是对onek表执行排序和顺序扫描，因为需要在这个表中
查询多条数据。因为索引扫描需要访问不连续的磁盘，所以索引扫描多条数据时会频繁使用排序顺序扫描（Sequential-scan-and-sort）。
有一种
方法可以看到不同的规划，就是强制规划器忽略任何策略。例如，如果我们不相信排序顺序扫描（sequential-scan-and-sort）是最好的办法，我们可以尝试这样的做法：
SETenable_sort=off;
Join(cost=0.56..292.65IndexScanusingonek_unique2ononekt2(cost=0.28..224.79rows=1000width=244)
显示测结果表明，规划器认为索引扫描比排序顺序扫描消耗高12%。当然下一个问题就是规划器的评估为什么是正确的。我们可以通过EXPLAIN ANALYZE进行考察。
EXPLAIN ANALYZE
通过EXPLAIN ANALYZE可以检查规划器评估的准确性。使用ANALYZE选项，EXPLAIN实际运行
查询，
显示真实的返回记录数和运行每个规划节点的时间，例如我们可以得到下面的结果：
EXPLAINANALYZE---------------------------------------------------------------------------------------------------------------------------------
rows=10width=488)(actualtime=0.128..0.377rows=10loops=1)
rows=10width=244)(actualtime=0.057..0.121rows=10width=0)(actualtime=0.024..0.024rows=1width=244)(actualtime=0.021..0.022rows=1loops=10)
IndexCond:(unique2=t1.unique2)
Totalruntime:0.501ms
注意，实际时间(actual time)的值是已毫秒为单位的实际时间，cost是评估的消耗，是个虚拟单位时间，所以他们看起来不匹配。
通常最重要的是看评估的记录数是否和实际得到的记录数接近。在这个例子里评估数完全和实际一样，但这种情况很少出现。
某些
查询规划可能执行多次子规划。比如之前提过的内循环规划（nested-loop），内部索引扫描的
次数是外部数据的
数量。在这种情况下，报告
显示循环执行的总
次数、平均实际执行时间和数据条数。这样做是为了和评估值表示方式一至。由循环
次数和平均值相乘得到总
消耗时间。
某些情况EXPLAIN ANALYZE会
显示额外的信息，比如sort和hash节点的时候：
ANDt1.unique2=t2.unique2ORDERBYt1.fivethous;
--------------------------------------------------------------------------------------------------------------------------------------------
Sort(cost=717.34..717.59rows=101width=488)(actualtime=7.761..7.774rows=100loops=1)
Key:t1.fivethous
SortMethod:quicksortMemory:77kB
->Hashtime=0.711..7.427rows=10000width=244)(actualtime=0.007..2.583rows=10000loops=1)
rows=101width=244)(actualtime=0.659..0.659 Buckets:1024Batches:1MemoryUsage:28kB
time=0.080..0.526rows=101width=0)(actualtime=0.049..0.049 Totalruntime:8.008ms
排序节点（Sort）显示排序类型（一般是在内存还是在磁盘）和使用多少内存。哈希节点（Hash）显示哈希桶和批数以及使用内存的峰值。
另一种额外信息是过滤条件过滤掉的记录数：
WHEREten<7;
---------------------------------------------------------------------------------------------------------
rows=7000width=244)(actualtime=0.016..5.107rows=7000loops=1)
Filter:(ten<7)
RowsRemovedbyFilter:3000
Totalruntime:5.905ms
这个值在join节点上尤其有价值。"Rows Removed"只有在过滤条件过滤掉数据时才显示。
类似条件过滤的情况也会在"lossy"索引扫描时发生，比如这样一个
查询，一个多边形含有的特定的点：
FROMpolygon_tblWHEREf1@>polygon'(0.5,2.0)';
------------------------------------------------------------------------------------------------------
onpolygon_tbl(cost=0.00..1.05rows=1width=32)(actualtime=0.044..0.044rows=0loops=1)
Filter:(f1@>'((0.5,2))'::polygon)
byFilter:4
Totalruntime:0.083ms
规划器认为（正确的）这样的表太小以至于不需要索引扫描，所以采用顺序扫描所有行经行条件检查。
但是，如果我们强制使用索引扫描，将会看到：
SETenable_seqscanTO--------------------------------------------------------------------------------------------------------------------------
IndexScanusinggpolygonindonpolygon_tbl(cost=0.13..8.15time=0.062..0.062IndexCond:(f1@>'((0.5,85); font-weight:bold">byIndexRecheck:1
Totalruntime:0.144ms
这里我们可以看到索引返回一条候选数据，但被过滤条件拒绝。这是因为GiST索引在多边形包含检测上是松散的"lossy"：它实际返回哪些和多边形交叠的数据，然后我们还需要针对这些数据做包含检测。
EXPLAIN还有BUFFERS选项可以和ANALYZE一起使用，来得到更多的运行时间分析：
EXPLAIN(ANALYZE,BUFFERS)time=0.323..0.342 Buffers:sharedhit=15
time=0.309..0.309 Buffers:sharedhit=7
time=0.043..0.043 Buffers:sharedhit=2
rows=999width=0)(actualtime=0.227..0.227rows=999loops=1)
Buffers:sharedhit=5
Totalruntime:0.423ms
Buffers提供的数据可以帮助确定哪些
查询是I/O密集型的。
请注意EXPLAIN ANALYZE实际运行
查询，任何实际影响都会发生。如果要分析一个
修改数据的
查询又不想改变你的表，你可以使用roll back命令进行回滚，比如：
BEGIN;
UPDATEtenk1SEThundred=hundred+1--------------------------------------------------------------------------------------------------------------------------------
Updateontenk1(cost=5.07..229.46rows=101width=250)(actualtime=14.628..14.628time=0.101..0.439 Totalruntime:14.727ms
ROLLBACK;
当查询是INSERT,UPDATE或DELETE命令时，在顶级节点是实施对表的变更。在这下面的节点实行定位旧数据计算新数据的工作。所以我们看到一样的bitmap索引扫描，并返回给Update节点。值得注意的是虽然修改数据的节点可能需要相当长的运行时间（在这里它消耗了大部分的时间），规划器却没有再评估时间中添加任何消耗，这是因为更新工作对于任何查询规划都是一样的，所以并不影响规划器的决策。
EXPLAIN ANALYZE的"Total runtime"
包括执行启动和
关闭时间，以及运行被激发的任何处触发器的时间，但不
包括分析、重写或规划时间。执行时间
包括BEFORE触发器，但不
包括AFTER触发器，因为AFTER是在
查询运行结束之后才触发的。每个触发器（无论BEFORE还是AFTER）的时间也会单独
显示出来。注意，延迟的触发器在事务结束前都不会被执行，所以EXPLAIN ANALYZE不会
显示。
