PostgreSql的Explain命令详解
程序员文章站
2022-07-06 12:02:05
http://toplchx.iteye.com/blog/2091860 使用EXPLAIN PostgreSQL为每个收到的查询设计一个查询规划。选择正确的匹配查询结构和数据属性的规划对执行效率是至关重要要的,所以系统包含一个复杂的规划器来试图选择好的规划。你可以使用EXPLAIN命令查看查询规 ......
http://toplchx.iteye.com/blog/2091860
使用explain
postgresql为每个收到的查询设计一个查询规划。选择正确的匹配查询结构和数据属性的规划对执行效率是至关重要要的,所以系统包含一个复杂的规划器来试图选择好的规划。你可以使用explain命令查看查询规划器创建的任何查询。阅读查询规划是一门艺术,需要掌握一定的经验,本节试图涵盖一些基础知识。
以下的例子来自postgresql 9.3开发版。
explain基础
查询规划是以规划为节点的树形结构。树的最底节点是扫描节点:他返回表中的原数据行。
不同的表有不同的扫描节点类型:顺序扫描,索引扫描和位图索引扫描。
也有非表列源,如values子句并设置from返回,他们有自己的扫描类型。
如果查询需要关联,聚合,排序或其他操作,会在扫描节点之上增加节点执行这些操作。通常有不只一种可能的方式做这些操作,所以可能出现不同的节点类型。
explain的输出是每个树节点显示一行,内容是基本节点类型和执行节点的消耗评估。可能会楚翔其他行,从汇总行节点缩进显示节点的其他属性。第一行(最上节点的汇总行)是评估执行计划的总消耗,这个值越小越好。
下面是一个简单的例子:
- explain select * from tenk1;
- query plan
- -------------------------------------------------------------
- seq scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
- 评估开始消耗。这是可以开始输出前的时间,比如排序节点的排序的时间。
- 评估总消耗。假设查询从执行到结束的时间。有时父节点可能停止这个过程,比如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
现在我们将查询加上where子句:
- explain select * from tenk1 where unique1 < 7000;
- query plan
- ------------------------------------------------------------
- seq scan on tenk1 (cost=0.00..483.00 rows=7001 width=244)
- filter: (unique1 < 7000)
查询节点增加了“filter”条件。这意味着查询节点为扫描的每一行数据增加条件检查,只输入符合条件数据。评估的输出记录数因为where子句变少了,但是扫描的数据还是10000条,所以消耗没有减少,反而增加了一点cup的计算时间。
这个查询实际输出的记录数是7000,但是评估是个近似值,多次运行可能略有差别,这中情况可以通过analyze命令改善。
现在再修改一下条件
- explain select * from tenk1 where unique1 < 100;
- query plan
- ------------------------------------------------------------------------------
- bitmap heap scan on tenk1 (cost=5.07..229.20 rows=101 width=244)
- recheck cond: (unique1 < 100)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
- index cond: (unique1 < 100)
现在,增加另一个查询条件:
- explain select * from tenk1 where unique1 < 100 and stringu1 = 'xxx';
- query plan
- ------------------------------------------------------------------------------
- bitmap heap scan on tenk1 (cost=5.04..229.43 rows=1 width=244)
- recheck cond: (unique1 < 100)
- filter: (stringu1 = 'xxx'::name)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
- index cond: (unique1 < 100)
如果在不同的字段上有独立的索引,规划器可能选择使用and或者or组合索引:
- explain select * from tenk1 where unique1 < 100 and unique2 > 9000;
- query plan
- -------------------------------------------------------------------------------------
- bitmap heap scan on tenk1 (cost=25.08..60.21 rows=10 width=244)
- recheck cond: ((unique1 < 100) and (unique2 > 9000))
- -> bitmapand (cost=25.08..25.08 rows=10 width=0)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
- index cond: (unique1 < 100)
- -> bitmap index scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
- index cond: (unique2 > 9000)
下面我们来看看limit的影响:
- explain select * from tenk1 where unique1 < 100 and unique2 > 9000 limit 2;
- query plan
- -------------------------------------------------------------------------------------
- limit (cost=0.29..14.48 rows=2 width=244)
- -> index scan using tenk1_unique2 on tenk1 (cost=0.29..71.27 rows=10 width=244)
- index cond: (unique2 > 9000)
- filter: (unique1 < 100)
来看一下通过索引字段的表连接:
- explain select *
- from tenk1 t1, tenk2 t2
- where t1.unique1 < 10 and t1.unique2 = t2.unique2;
- query plan
- --------------------------------------------------------------------------------------
- nested loop (cost=4.65..118.62 rows=10 width=488)
- -> bitmap heap scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
- recheck cond: (unique1 < 10)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
- index cond: (unique1 < 10)
- -> index scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244)
- index cond: (unique2 = t1.unique2)
外部节点的消耗加上循环内部节点的消耗(39.47+10*7.91)再加一点cpu时间就得到规划的总消耗。
再看一个例子:
- explain select *
- from tenk1 t1, tenk2 t2
- where t1.unique1 < 10 and t2.unique2 < 10 and t1.hundred < t2.hundred;
- query plan
- ---------------------------------------------------------------------------------------------
- nested loop (cost=4.65..49.46 rows=33 width=488)
- join filter: (t1.hundred < t2.hundred)
- -> bitmap heap scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
- recheck cond: (unique1 < 10)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
- index cond: (unique1 < 10)
- -> materialize (cost=0.29..8.51 rows=10 width=244)
- -> index scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
- index cond: (unique2 < 10)
注意这次规划器选择使用meaterialize节点,将条件加入内部节点,这以为着内部节点的索引扫描只做一次,即使嵌套循环需要读取这些数据10次,meterialize节点将数据保存在内存中,每次循环都从内存中读取数据。
如果我们稍微改变一下查询,会看到完全不同的规划:
- explain select *
- from tenk1 t1, tenk2 t2
- where t1.unique1 < 100 and t1.unique2 = t2.unique2;
- query plan
- ------------------------------------------------------------------------------------------
- hash join (cost=230.47..713.98 rows=101 width=488)
- hash cond: (t2.unique2 = t1.unique2)
- -> seq scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
- -> hash (cost=229.20..229.20 rows=101 width=244)
- -> bitmap heap scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)
- recheck cond: (unique1 < 100)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
- index cond: (unique1 < 100)
注意缩进反应的规划结构。在tenk1表上的bitmap扫描结果作为hash节点的输入建立哈希表。然后hash join节点读取外层子节点的数据,再循环检索哈希表的数据。
另一个可能的连接类型是merge join:
- explain select *
- from tenk1 t1, onek t2
- where t1.unique1 < 100 and t1.unique2 = t2.unique2;
- query plan
- ------------------------------------------------------------------------------------------
- merge join (cost=198.11..268.19 rows=10 width=488)
- merge cond: (t1.unique2 = t2.unique2)
- -> index scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
- filter: (unique1 < 100)
- -> sort (cost=197.83..200.33 rows=1000 width=244)
- sort key: t2.unique2
- -> seq scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)
有一种方法可以看到不同的规划,就是强制规划器忽略任何策略。例如,如果我们不相信排序顺序扫描(sequential-scan-and-sort)是最好的办法,我们可以尝试这样的做法:
- set enable_sort = off;
- explain select *
- from tenk1 t1, onek t2
- where t1.unique1 < 100 and t1.unique2 = t2.unique2;
- query plan
- ------------------------------------------------------------------------------------------
- merge join (cost=0.56..292.65 rows=10 width=488)
- merge cond: (t1.unique2 = t2.unique2)
- -> index scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
- filter: (unique1 < 100)
- -> index scan using onek_unique2 on onek t2 (cost=0.28..224.79 rows=1000 width=244)
explain analyze
通过explain analyze可以检查规划器评估的准确性。使用analyze选项,explain实际运行查询,显示真实的返回记录数和运行每个规划节点的时间,例如我们可以得到下面的结果:
- explain analyze select *
- from tenk1 t1, tenk2 t2
- where t1.unique1 < 10 and t1.unique2 = t2.unique2;
- query plan
- ---------------------------------------------------------------------------------------------------------------------------------
- nested loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
- -> bitmap heap scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
- recheck cond: (unique1 < 10)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
- index cond: (unique1 < 10)
- -> index scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
- index cond: (unique2 = t1.unique2)
- total runtime: 0.501 ms
通常最重要的是看评估的记录数是否和实际得到的记录数接近。在这个例子里评估数完全和实际一样,但这种情况很少出现。
某些查询规划可能执行多次子规划。比如之前提过的内循环规划(nested-loop),内部索引扫描的次数是外部数据的数量。在这种情况下,报告显示循环执行的总次数、平均实际执行时间和数据条数。这样做是为了和评估值表示方式一至。由循环次数和平均值相乘得到总消耗时间。
某些情况explain analyze会显示额外的信息,比如sort和hash节点的时候:
- explain analyze select *
- from tenk1 t1, tenk2 t2
- where t1.unique1 < 100 and t1.unique2 = t2.unique2 order by t1.fivethous;
- query plan
- --------------------------------------------------------------------------------------------------------------------------------------------
- sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
- sort key: t1.fivethous
- sort method: quicksort memory: 77kb
- -> hash join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
- hash cond: (t2.unique2 = t1.unique2)
- -> seq scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
- -> hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
- buckets: 1024 batches: 1 memory usage: 28kb
- -> bitmap heap scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
- recheck cond: (unique1 < 100)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
- index cond: (unique1 < 100)
- total runtime: 8.008 ms
另一种额外信息是过滤条件过滤掉的记录数:
- explain analyze select * from tenk1 where ten < 7;
- query plan
- ---------------------------------------------------------------------------------------------------------
- seq scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
- filter: (ten < 7)
- rows removed by filter: 3000
- total runtime: 5.905 ms
类似条件过滤的情况也会在"lossy"索引扫描时发生,比如这样一个查询,一个多边形含有的特定的点:
- explain analyze select * from polygon_tbl where f1 @> polygon '(0.5,2.0)';
- query plan
- ------------------------------------------------------------------------------------------------------
- seq scan on polygon_tbl (cost=0.00..1.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
- filter: (f1 @> '((0.5,2))'::polygon)
- rows removed by filter: 4
- total runtime: 0.083 ms
但是,如果我们强制使用索引扫描,将会看到:
- set enable_seqscan to off;
- explain analyze select * from polygon_tbl where f1 @> polygon '(0.5,2.0)';
- query plan
- --------------------------------------------------------------------------------------------------------------------------
- index scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
- index cond: (f1 @> '((0.5,2))'::polygon)
- rows removed by index recheck: 1
- total runtime: 0.144 ms
explain还有buffers选项可以和analyze一起使用,来得到更多的运行时间分析:
- explain (analyze, buffers) select * from tenk1 where unique1 < 100 and unique2 > 9000;
- query plan
- ---------------------------------------------------------------------------------------------------------------------------------
- bitmap heap scan on tenk1 (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
- recheck cond: ((unique1 < 100) and (unique2 > 9000))
- buffers: shared hit=15
- -> bitmapand (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
- buffers: shared hit=7
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
- index cond: (unique1 < 100)
- buffers: shared hit=2
- -> bitmap index scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)
- index cond: (unique2 > 9000)
- buffers: shared hit=5
- total runtime: 0.423 ms
buffers提供的数据可以帮助确定哪些查询是i/o密集型的。
请注意explain analyze实际运行查询,任何实际影响都会发生。如果要分析一个修改数据的查询又不想改变你的表,你可以使用roll back命令进行回滚,比如:
- begin;
- explain analyze update tenk1 set hundred = hundred + 1 where unique1 < 100;
- query plan
- --------------------------------------------------------------------------------------------------------------------------------
- update on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=14.628..14.628 rows=0 loops=1)
- -> bitmap heap scan on tenk1 (cost=5.07..229.46 rows=101 width=250) (actual time=0.101..0.439 rows=100 loops=1)
- recheck cond: (unique1 < 100)
- -> bitmap index scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
- index cond: (unique1 < 100)
- total runtime: 14.727 ms
- rollback;
explain analyze的"total runtime"包括执行启动和关闭时间,以及运行被激发的任何处触发器的时间,但不包括分析、重写或规划时间。执行时间包括before触发器,但不包括after触发器,因为after是在查询运行结束之后才触发的。每个触发器(无论before还是after)的时间也会单独显示出来。注意,延迟的触发器在事务结束前都不会被执行,所以explain analyze不会显示。
上一篇: iOS 数据存储
下一篇: iOS: MJRefresh源码分析