POSTGRESQL 怎么通过explain 来分析SQL查询性能

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在聚合中使用中会比较有利.