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

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