pgsql

EXPLAIN分析pgsql的性能

EXPLAIN分析pgsql的性能

Posted by liz on April 26, 2020

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

CATALOG
    FEATURED TAGS
    pgsql go PostgreSQL docker k8s etcd raft 设计模式 算法 消息队列 Redis plan tcp/ip MySQL MongoDB 架构
    FRIENDS