Skip to main content
 首页 » 数据库

PostgreSQL EXPLAIN分析pgsql的性能

2022年07月19日136fff_TT

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更有效
缺点 当索引丢失或者查询条件限制不够时,效率很低;当表的纪录数多时,效率低。 为建立哈希表,需要大量内存。第一次的结果返回较慢。 所有的表都需要排序。它为最优化的吞吐量而设计,并且在结果没有全部找到前不返回数据。

参考

【EXPLAIN】http://www.postgres.cn/docs/9.5/sql-explain.html


本文参考链接:https://www.cnblogs.com/ricklz/p/12777165.html
阅读延展