EXPLAIN分析pgsql的性能
EXPLAIN分析pgsql的性能
前言
对于pgsql中查询性能的分析，好像不想mysql中那么简单。当然pgsql中也是通过EXPLAIN进行分析，那么就来认真中结下pgsql中explain的使用。
EXPLAIN命令
EXPLAIN -- 显示一个语句的执行计划
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
这里 option可以是：
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
TIMING [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }
ANALYZE选项通过实际执行的sql来获取相应的计划。这个是真正执行的，多以可以真是的看到执行计划花费了多少的时间，还有它返回的行数。
当然对于分析插入更新的语句，我们我们是可以把ANALYZE放到事物里面的，分析后之后回滚。
BEGIN;
EXPLAIN ANALYZE ...;
ROLLBACK;
命令详解
VERBOSE选项用于显示计划的附加信息。这些附加的信息有：计划中每个节点输出的各个列，如果触发器被触发，还会输出触发器的名称。该选项默认为FALSE。
COSTS选项显示每个计划节点的启动成本和总成本，以及估计行数和每行宽度。该选项默认是TRUE。
BUFFERS选项显示关于缓存区的信息。该选项只能与ANALYZE参数一起使用。显示的缓存区信息包括共享快块，本地块和临时读和写的块数。共享块、本地块和临时块分别包含表和索引、临时快和临时索引、以及在排序和物化计划中使用的磁盘块。上层节点显示出来的数据块包含其所有子节点使用的块数。该选项默认为FALSE。
EXPLAIN输出结果展示
explain select * from test1
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33)
Seq Scan 表示的是全表扫描，就是从头扫尾扫描一遍表里面的数据。
(cost=0.00..146666.56 rows=7999956 width=33)的内容可以分成三部分
- cost=0.00..146666.56 cost后面有两个数字,中间使用
..分割，第一个数字0.00表示启动的成本，也就是返回第一行需要多少cost值；第二行表示返回所有数据的成本。 - rows=7999956:表示会返回7999956行
- width=33:表示每行的数据宽度为33字节
其中cost描述的是一个sql执行的代价。
analyze
通过analyze可以看到更加精确的执行计划。
explain analyze select * from test1
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33) (actual time=0.012..1153.317 rows=8000001 loops=1)
Planning time: 0.049 ms
Execution time: 1637.480 ms
加了analyze可以看到实际的启动时间，(actual time=0.012..1153.317 rows=8000001 loops=1)其中:
- actual time=0.012..1153.317:
0.012表示的是启动的时间,..后面的时间表示返回所有行需要的时间 - rows=8000001:表示返回的行数
buffers
通过使用buffers来查看缓存区命中的情况
explain (analyze,buffers) select * from test1
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..146666.56 rows=7999956 width=33) (actual time=0.013..1166.464 rows=8000001 loops=1)
Buffers: shared hit=777 read=65890
Planning time: 0.049 ms
Execution time: 1747.163 ms
其中会多出一行Buffers: shared hit=777 read=65890。
- shared hit=777：表示的在共享内存里面直接读到
777个块； - read=65890：表示从磁盘中读了
65890块 - written: 表示写磁盘共
xx块
通过上面数字进行分析，我们可以知道那些部分是对I/O最敏感的。
全表扫描
全表扫描在pgsql中叫做顺序扫描(seq scan)，全表扫描就是把表的的所有的数据从头到尾读取一遍，然后从数据块中找到符合条件的数据块。
索引扫描
索引是为了加快数据查询的速度索引而增加的(Index Scan)。索引扫描也就是我们的查询条件使用到了我们创建的索引，当然什么是索引，自行查阅资料吧。
位图扫描
位图扫描也是走索引的一种方式。方法是扫描索引，那满足条件的行或者块在内存中建立一个位图，扫描完索引后，再根据位图到表的数据文件中把相应的数据读出来。如果走了两个索引，可以把两个索引进行’and‘或‘or’计算，合并到一个位图，再到表的数据文件中把数据读出来。
条件过滤
就是在where后面加上过滤条件，扫描数据行，会找出满足条件过滤的行。条件过滤执行计划中显示为‘Filter’。
Nestloop join
对于被连接的数据子集较小的情况，Nested Loop是个较好的选择。Nested Loop是连表查询最朴素的一种连接方式。在嵌套循环的时候，内表被外表驱动，外表中返回的每一行，都要在内表中检索找寻和它匹配的行。整个查询返回的结果集不能太大（>10000不适合），要把返回子集比较小的表作为外表，同时内表中连接查询的字段，最好能命中索引，不然会有性能问题。
执行的过程：确定一个驱动表（outer table），另一个表为inner table，驱动表中的每一行数据会去inner表中，查找检索数据。注意，驱动表的每一行都去inner表中检索，索引驱动表的数据不能太大。对于inner表中的数据就没有限制了，只要创建的索引合适，inner表中数据的大小对查询的性能影响不大。
测试下：
创建数据表
create table test1
(
id bigserial not null
constraint test1_pk
primary key,
name text not null,
category_id bigint not null
);
create index test1_category_id_index
on test1 (category_id);
create table test2
(
id bigserial not null
constraint test2_pk
primary key,
name text not null,
category_id bigint not null
);
create index test2_category_id_index
on test2 (category_id);
插入数据,test1插入8000000条，test2插入6000000条
do $$
declare
v_idx integer := 1;
begin
while v_idx < 8000000 loop
v_idx = v_idx+1;
insert into test1 (name, category_id) values ( random()*(20000000000000000-10)+10,random()*(8000000-10)+10);
end loop;
end $$;
do $$
declare
v_idx integer := 1;
begin
while v_idx < 6000000 loop
v_idx = v_idx+1;
insert into test2 (name, category_id) values ( random()*(20000000000000000-10)+10,random()*(6000000-10)+10);
end loop;
end $$;
验证下查询
explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id and a.id<10000
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Gather (cost=1170.58..67037.87 rows=14666 width=16)
Workers Planned: 2
-> Nested Loop (cost=170.58..64571.27 rows=6111 width=16)
-> Parallel Bitmap Heap Scan on test1 a (cost=170.15..24939.15 rows=3748 width=16)
Recheck Cond: (id < 10000)
-> Bitmap Index Scan on test1_pk (cost=0.00..167.90 rows=8996 width=0)
Index Cond: (id < 10000)
-> Index Scan using test2_category_id_index on test2 b (cost=0.43..10.55 rows=2 width=16)
Index Cond: (category_id = a.category_id)
可以看到当有高选择性索引或进行限制性，查询优化器会自动选择Nested Loop
Hash join
优化器使用两个表较小的表，并利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的列。
这种方式适用于较小的表可以完全放入到内存中，这样总成本就是访问两个表的成本之和。但是如果表都很大，不能放入到内存中，优化器会将它分割成若干个不同的分区，把不能放入到内存的部分写入到临时段。此时要求有较大的临时段从而尽量提高I/O 的性能。它能够很好的工作于没有索引的大表和并行查询的环境中，并提供最好的性能。
优化器会自动选择较小的表，建立散列表，然后扫描另个较大的表。Hash Join只能应用于等值连接(如WHERE A.COL3 = B.COL4)，这是由Hash的特点决定的。
测试下
drop index test1_category_id_index;
drop index test2_category_id_index;
删除关联查询的字段的索引
explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id and a.id<10000
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=214297.39..248684.97 rows=14666 width=16)
Hash Cond: (a.category_id = b.category_id)
-> Index Scan using test1_pk on test1 a (cost=0.43..335.86 rows=8996 width=16)
Index Cond: (id < 10000)
-> Hash (cost=109999.98..109999.98 rows=5999998 width=16)
-> Seq Scan on test2 b (cost=0.00..109999.98 rows=5999998 width=16)
因为没有索引了，并且查询的条数可以完全放入到内存里面，所以查询优化器就选择使用Hash join了，对于选择那个表建立散列表，要看查询的条件。如上面的限制条件a.id<10000,限制了a表查询的数据条数，那么a表条数较少，然后就在a表建立散列表，然后扫描b表。
Merge Join
通常情况下散列连接的效果比合并连接的效果好，如果源数据上有索引，或者结果已经排过序，在执行顺序合并连接时就不需要排序了，这时合并连接的性能会优于散列连接。
Merge join的操作步骤：
1' 对连接的每个表做table access full;
2' 对table access full的结果进行排序;
3' 进行merge join对排序结果进行合并。
Merge Join可适于于非等值Join（>，<，>=，<=，但是不包含!=，也即<>）
explain select a.id,b.id from test1 a,test2 b where a.category_id=b.category_id
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Merge Join (cost=2342944.84..2575285.64 rows=13041968 width=16)
Merge Cond: (b.category_id = a.category_id)
-> Sort (cost=1005574.67..1020574.66 rows=5999998 width=16)
Sort Key: b.category_id
-> Seq Scan on test2 b (cost=0.00..124999.98 rows=5999998 width=16)
Filter: (id < 100000000)
-> Materialize (cost=1337364.94..1377364.72 rows=7999956 width=16)
-> Sort (cost=1337364.94..1357364.83 rows=7999956 width=16)
Sort Key: a.category_id
-> Seq Scan on test1 a (cost=0.00..146666.56 rows=7999956 width=16)
category_id上面是没有索引的，这时候查询选择了Merge Join，上面的Sort Key: a.category_id，就是对a表的category_id字段排序。
Nested Loop，Hash JOin，Merge Join对比
| 类别 | Nested Loop | Hash Join | Merge Join |
|---|---|---|---|
| 使用条件 | 任何条件 | 等值连接（=） | 等值或非等值连接(>，<，=，>=，<=)，‘<>’除外 |
| 相关资源 | CPU、磁盘I/O | 内存、临时空间 | 内存、临时空间 |
| 特点 | 当有高选择性索引或进行限制性搜索时效率比较高，能够快速返回第一次的搜索结果。 | 当缺乏索引或者索引条件模糊时，Hash Join比Nested Loop有效。通常比Merge Join快,如果有索引，或者结果已经被排序了，这时候Merge Join的查询更快。在数据仓库环境下，如果表的纪录数多，效率高。 | 当缺乏索引或者索引条件模糊时，Merge Join比Nested Loop有效。非等值连接时，Merge Join比Hash Join更有效 |
| 缺点 | 当索引丢失或者查询条件限制不够时，效率很低；当表的纪录数多时，效率低。 | 为建立哈希表，需要大量内存。第一次的结果返回较慢。 | 所有的表都需要排序。它为最优化的吞吐量而设计，并且在结果没有全部找到前不返回数据。 |
