Explain 命令是大多数数据库常用的一种展示SQL 执行计划和cost 的一种方式。在POSTGRESQL 中EXPLAIN 命令展示的信息比较详细，并且附带explain有不少的附加的命令来进行更多的展示。从命令来命令和功能来划分
explain select a.first_name,a.last_name,a.last_update,fa.film_id from film_actor as fa right join actor as a on fa.actor_id = a.actor_id where fa.actor_id < 6;
QUERY PLAN
--------------------------------------------------------------------------------------------
Gather (cost=10.71..42.20 rows=100 width=23)
Workers Planned: 3
-> Parallel Hash Join (cost=9.71..40.20 rows=32 width=23)
Hash Cond: (fa.actor_id = a.actor_id)
-> Parallel Bitmap Heap Scan on film_actor fa (cost=5.06..35.46 rows=32 width=4)
Recheck Cond: (actor_id < 6)
-> Bitmap Index Scan on film_actor_pkey (cost=0.00..5.03 rows=100 width=0)
Index Cond: (actor_id < 6)
-> Parallel Hash (cost=3.18..3.18 rows=118 width=25)
-> Parallel Seq Scan on actor a (cost=0.00..3.18 rows=118 width=25)
(10 rows)查看explain 计划的方式和ORACLE 的查看方式类似, 从里到外,从下到上
查询中的顺序通过上图可以看出
1 通过 film_actor表的条件将actor_id where 条件先进行执行, 控制参与数据查询的数据量,并且通过主键的方式获得数据,使用索引的方式是通过bitmap 的方式来进行
2 并行从film_actor 获取的数据通过并行的方式与actor表的数据进行HASH JOIN
3 最后聚合结果
通过analyze 附加的参数实际执行整体的时间在16毫秒. 并且cost 的值最上层是总的执行计划耗费的,每个子步骤有自己的cost 的消耗说明.
而cost 中的前面的数值和后面的数值分别代表, 查询计划在获取第一行数据的成本和获取所有数据后的成本.
当通过添加verbose 命令后,显示的信息更加的详细,并发work中如何进行并行工作,使用了多少CACHE 也会进行显示.
explain (analyze ,verbose,buffers) select a.first_name,a.last_name,a.last_update,fa.film_id from film_actor as fa right join actor as a on fa.actor_id = a.actor_id where fa.actor_id < 6 order by fa.actor_id;
添加buffers 参数后,展示的计划中会添加在buffers 中命中的page 的数量.
剩下的就是对EXPLAIN 中的展示项进行理解: 如
1 Seq Scan: 针对表进行全表扫描, 这一般就需要看看是否有优化的必要了
2 Index Scan: 根据索引来进行索引扫描,通过索引扫描来进行数据的筛选
3 Index Only Scan (since PostgreSQL 9.2) : 通过索引查询并且仅仅通过索引就可以满足查询的数据需求,相关减少索引查询后的回表问题
4 Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond 相对于index scan, bitmap index scan 的有点在于, 他一次性将索引指向行的指针搜索完毕,并且在内存中生成相关的指针地图,然后在一次性的将数据根据地图获取. 性能比index scan 要好.
5 Nested Loops : Nested Loops 是两张表之间根据之间的关联关系进行数据的fetch, 基本原理是分为驱动表和数据表, 从驱动表中取出一条数据,与数据表的逐行数据进行对比,并查找到结果进行缓存, 相当于一个双循环的结构.在数据库中这样的多表查询方式是低级的. 对于小数据量的多表之间的查询简单快速,耗费的执行计划计算的时间少.
6 HASH JOIN , 通过将表中关键字段的hash值进行计算后, 通过将计算后的值与另一张表进行散列表的计算,获得对应的数据,对于表连接来说快速查询数据是有利的.
7 Merge Join, Merge join 在商业数据库中对于表连接也是大量使用,通过对两个表的对应关系列进行排序,然后进行快速的对比,找到符合数据tuple 也是一种快速的进行表JOIN 的快速查询的方法.
8 Sort / Sort Key 通过对数据汇聚后在内存中进行排序,消耗内存较大
9 GroupAggregate : 在查询中使用GROUP BY 语句会在执行计划中出现groupaggregate 操作
10 HashAggregate : 通过临时表来将数据进行hash 临时存储,在计算中不需要较大的内存
在进行group by 的操作中,如果GROUP BY 键有索引, 会倾向性的使用groupAggregate 而如果GROUP BY 键没有索引,则HASHAggregate在聚合中使用中会比较有利.
