Postgresql explain 使用手册
postgresql为每一个查询产生一个查询计划，选择正确的查询结构(通过系统统计采样)和数据属性（字段类型）对于提升性能非常关键。
系统准备了一个非常复杂的规划器 来选择开销最小的计划。
可以使用EXPLAIN来查看规划器为每个查询生成的查询计划是什么。
本例中，是使用默认的TEXT输出,其他输出,比如XML,JSON,YAML,更有利于机器读取和分析。
本例以一张记录攻击IP地址表tb_ip_attack为例，上面记载了各类猜测密码的ip地址，当然这些表内容并不关键。
下面的例子展示了json格式的返回内容
weather=# explain(format json) select * from tb_ip_attack limit 10;
QUERY PLAN
--------------------------------------------
[ +
{ +
"Plan": { +
"Node Type": "Limit", +
"Parallel Aware": false, +
"Startup Cost": 0.00, +
"Total Cost": 0.40, +
"Plan Rows": 10, +
"Plan Width": 201, +
"Plans": [ +
{ +
"Node Type": "Seq Scan", +
"Parent Relationship": "Outer", +
"Parallel Aware": false, +
"Relation Name": "tb_ip_attack",+
"Alias": "tb_ip_attack", +
"Startup Cost": 0.00, +
"Total Cost": 1745.15, +
"Plan Rows": 43615, +
"Plan Width": 201 +
} +
] +
} +
} +
]
(1 row)
1. EXPLAIN 基础
查询规划是一个计划节点的树.
最底层的节点是扫描节点,它们从表中返回未经处理的行.
不同的表有不同的扫描节点类型:顺序扫描,索引扫描,位图索引扫描.除此之外,还有非表行来源,比如VALUE子句和FROM中返回集合的函数,它们有自己的扫描节点类型.
如果查询需要连接,聚集,排序或者对原始行的其他操作,那么就会在扫描节点上有其他额外的节点来执行这些操作.并且,这些操作通常有多种方法,因此在这些位置,也会出现不同的节点类型.
EXPLAIN会为每一个节点输出一行,显示基本的节点类型和规划器为该计划节点的执行所做的开销估计.
- 可能会出现其他的行,从节点的摘要处进行缩进,以显示该节点的其他属性
- 第一行(最顶层的摘要行),是对该规划的总执行的开销估计,规划器尝试最小化的,就是这个数字.
这里举例
weather=# explain select * from tb_ip_attack;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on tb_ip_attack (cost=0.00..1745.15 rows=43615 width=201)
(1 row)
由于这个查询没有WHERE子句,它必须扫描表中所有的行,因此规划器只能选择一个简单的顺序扫描Seq Scan.
括号中由左到右分别是
-
cost估计的启动开销,在输出阶段开始之前消耗的时间.例如在一个排序节点中执行排序的时间
..分割后第一项 -
cost..分割后第二项该节点估计的总开销
这个估计值基于的假设是,计划节点会被运行到完成,即所有可用的行都被检索.不过这种情况很可能被父节点停止读取所有可用行，比如
limitweather=# explain select * from tb_ip_attack limit 10; QUERY PLAN --------------------------------------------------------------------------- Limit (cost=0.00..0.40 rows=10 width=201) -> Seq Scan on tb_ip_attack (cost=0.00..1745.15 rows=43615 width=201) (2 rows) -
rows这个计划节点输出行数的估计值,同样,假定该节点可以完成. -
预计这个节点输出的行平均宽度,以字节为单位计算.
开销是用规划器的开销参数决定的任意的单位来衡量的.传统上是以磁盘页面抓取为单位来度量的.
也就是配置文件postgresql.conf中seq_page_cost配置项
一个上层节点的开销,包括它的所有子节点的开销
这个开销只反映规划器关心的东西,
特别是开销没有考虑结果行传递给客户端所花费的时间,这个时间可能是实际操作中非常重要的一个因素,但是规划器把它忽略了.
行数值rows有一些技巧,因为它不是计划节点处理或扫描过的行数,而是该节点发出的行数.通常会少于扫描的行数,因为被扫描的行数会被WHERE这样的子句过滤掉.
理想中顶层的行数估计会更接近于查询实际返回,更新,删除的行数.
回到前面的例子
weather=# explain select * from tb_ip_attack;
QUERY PLAN
---------------------------------------------------------------------
Seq Scan on tb_ip_attack (cost=0.00..1745.15 rows=43615 width=201)
(1 row)
查询系统视图，你会发现估算的开销非常明确
weather=# SELECT relpages, reltuples FROM pg_class WHERE relname = 'tb_ip_attack';
relpages | reltuples
----------+-----------
1309 | 43615
你会发现，tb_ip_attack有1006个磁盘页面page，和39960行。
开销计算公式为: **(页面读取数✖seq_page_cost) + (扫描的行数✖cpu_tuple_cost) **
默认情况下， seq_page_cost是1.0，cpu_tuple_cost是0.01，因此，估计的开销为(1309 * 1.0) + (43615 * 0.01) = 1745.15
- 注意，当你自己实践这些语句的时候，可能会发现计算的值和估算的不太一样，这是因为pg的脏页和统计信息问题，你可以使用
pg_repack或者vacuum full重做这张表，以得到和估算结果相同的计算结果
现在，我们加上一个筛选条件，WHERE子句如下
weather=# explain select * from tb_ip_attack where country = 'JP';
QUERY PLAN
------------------------------------------------------------------
Seq Scan on tb_ip_attack (cost=0.00..1854.19 rows=71 width=201)
Filter: (country = 'JP'::text)
值得注意的是，EXPLAIN输出WHERE子句被当作一个过滤器条件附加到顺序扫描计划节点。这意味着为它扫描的每一行作检查。
由于WHERE子句的存在，输出的行数 由43615行下降到了71行，但是开销cost并没有降低，反而升高了，准确的说，是增加了cpu_operator_cost✖扫描行数，以反映检查 WHERE条件所花费的额外 CPU 时间。
如果执行明确有限字段并且增加条件的查询，那么查询返回的开销会变化
weather=# explain select dt,visitor,ip_addr,port,country from tb_ip_attack where country = 'JP';
QUERY PLAN
-----------------------------------------------------------------
Seq Scan on tb_ip_attack (cost=0.00..1649.16 rows=66 width=47)
Filter: (country = 'JP'::text)
(2 rows)
下面我们将条件变为主键，更深一步了解EXPLAIN
weather=# explain select * from tb_ip_attack where id = '2047812b-b990-420d-89a7-480f8c8237ea';
QUERY PLAN
----------------------------------------------------------------------------------------
Index Scan using tb_ip_attack_pkey on tb_ip_attack (cost=0.41..8.43 rows=1 width=201)
Index Cond: (id = '2047812b-b990-420d-89a7-480f8c8237ea'::text)
(2 rows)
-
很明显，上述查询规划采用了主键索引。
索引扫描不同于顺序扫描，索引扫描需要非顺序的磁盘访问。
weather=# explain select * from tb_ip_attack where id = '2047812b-b990-420d-89a7-480f8c8237ea' and id = '308bb35b-d0de-4601-8a8e-56dbd44b8d24';
QUERY PLAN
----------------------------------------------------------------------------------------------
Result (cost=0.41..8.43 rows=1 width=201)
One-Time Filter: false
-> Index Scan using tb_ip_attack_pkey on tb_ip_attack (cost=0.41..8.43 rows=1 width=201)
Index Cond: (id = '2047812b-b990-420d-89a7-480f8c8237ea'::text)
(4 rows)
- 上例中，使用了一个且条件，主键是唯一的，所以当完成了左侧的规划后，发现合并条件有误，直接返回空值了。
weather=# explain select * from tb_ip_attack where id = '2047812b-b990-420d-89a7-480f8c8237ea' or id = '308bb35b-d0de-4601-8a8e-56dbd44b8d24';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb_ip_attack (cost=8.85..16.64 rows=2 width=201)
Recheck Cond: ((id = '2047812b-b990-420d-89a7-480f8c8237ea'::text) OR (id = '308bb35b-d0de-4601-8a8e-56dbd44b8d24'::text))
-> BitmapOr (cost=8.85..8.85 rows=2 width=0)
-> Bitmap Index Scan on tb_ip_attack_pkey (cost=0.00..4.42 rows=1 width=0)
Index Cond: (id = '2047812b-b990-420d-89a7-480f8c8237ea'::text)
-> Bitmap Index Scan on tb_ip_attack_pkey (cost=0.00..4.42 rows=1 width=0)
Index Cond: (id = '308bb35b-d0de-4601-8a8e-56dbd44b8d24'::text)
(7 rows)
- 使用或条件，则要走两次位图索引扫描，累计时间则是两次相加
如果我们改为另外一种方式，用in会更好么
weather=# explain select * from tb_ip_attack where id in('2047812b-b990-420d-89a7-480f8c8237ea' , '308bb35b-d0de-4601-8a8e-56dbd44b8d24');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb_ip_attack (cost=8.85..16.64 rows=2 width=201)
Recheck Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
-> Bitmap Index Scan on tb_ip_attack_pkey (cost=0.00..8.85 rows=2 width=0)
Index Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
(4 rows)
-
可以看到，计划先走了一遍索引扫描，然后又走了一遍recheck cond,时间估计是一样的。
recheck cond表示，为了避免位图过大，我们将其变为有损的样式，这里只记录元素匹配的页面page,而不是更底层的元组tuple，这种情况的意思就是，规划器需要重新在位图索引的页面中查找元素所在的元组。
2. EXPLAIN ANALYZE
可以通过使用EXPLAIN的ANALYZE选项来检查规划器估计值的准确性。 通过使用这个选项，EXPLAIN会实际执行该查询， 然后显示真实的行计数和在每个计划节点中累计的真实运行时间，还会有一个普通 EXPLAIN显示的估计值
例如
weather=# explain analyze select * from tb_ip_attack where id in('2047812b-b990-420d-89a7-480f8c8237ea' , '308bb35b-d0de-4601-8a8e-56dbd44b8d24');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb_ip_attack (cost=8.85..16.64 rows=2 width=201) (actual time=2.222..2.224 rows=2 loops=1)
Recheck Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
Heap Blocks: exact=1
-> Bitmap Index Scan on tb_ip_attack_pkey (cost=0.00..8.85 rows=2 width=0) (actual time=2.217..2.217 rows=2 loops=1)
Index Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
Planning time: 0.074 ms
Execution time: 2.284 ms
(7 rows)
actual time是以毫秒为单位的，而cost则是以page抓取单位和cpu开销等为单位的，这两个值不一样很正常。- 在某些查询计划中，一个子规划节点很可能运行多次。例如， 内层索引扫描将在上述嵌套循环计划中的每一个外层行执行一次。 在这种情况下，
loops值报告了该节点执行的总次数
EXPLAIN有一个BUFFERS选项可以和ANALYZE 一起使用来得到更多的运行时统计信息：
weather=# explain(analyze,buffers) select * from tb_ip_attack where id in('2047812b-b990-420d-89a7-480f8c8237ea' , '308bb35b-d0de-4601-8a8e-56dbd44b8d24');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tb_ip_attack (cost=8.85..16.64 rows=2 width=201) (actual time=0.026..0.026 rows=2 loops=1)
Recheck Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
Heap Blocks: exact=1
Buffers: shared hit=7
-> Bitmap Index Scan on tb_ip_attack_pkey (cost=0.00..8.85 rows=2 width=0) (actual time=0.021..0.022 rows=2 loops=1)
Index Cond: (id = ANY ('{2047812b-b990-420d-89a7-480f8c8237ea,308bb35b-d0de-4601-8a8e-56dbd44b8d24}'::text[]))
Buffers: shared hit=6
Planning time: 0.071 ms
Execution time: 0.060 ms
(9 rows)
BUFFERS提供的数字有助于识别查询的哪些部分是最I/O密集型的。
特别需要注意的
EXPLAIN ANALYZE实际运行查询，任何副作用都将照常发生，为了避免这一点
如果你想要分析一个数据修改查询而不想改变你的表，你可以将explain放在一个事务中，在分析完后回滚命令
weather=# begin;
BEGIN
weather=# explain analyze delete from tb_ip_attack where country = 'JP';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Delete on tb_ip_attack (cost=0.00..1854.19 rows=71 width=6) (actual time=7.161..7.162 rows=0 loops=1)
-> Seq Scan on tb_ip_attack (cost=0.00..1854.19 rows=71 width=6) (actual time=0.009..6.806 rows=225 loops=1)
Filter: (country = 'JP'::text)
Rows Removed by Filter: 43390
Planning time: 0.050 ms
Execution time: 7.185 ms
(6 rows)
weather=# rollback;
ROLLBACK
EXPLAIN结果不应该被外推到与你实际测试的场景非常不同的情况。例如， 一个很小的表上的结果不能被假定成适合大型表
规划器的开销估计不是线性的， 并且因此它可能为一个更大或更小的表选择一个不同的计划。一个极端例子是， 在一个只占据一个磁盘页面的表上，你将几乎总是得到一个顺序扫描计划，而不管索引是否可用。 规划器认识到它在任何情况下都将采用一次磁盘页面读取来处理该表， 因此再花费额外的页面读取去查找索引是没有价值的