postgresql 的pg_stat_statements blk_read_time指标问题

实验环境

select version();
                                                 version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-11), 64-bit
(1 row)

实验方法

创建一个800多M的表。

CREATE TABLE sampletable (x numeric);
 INSERT INTO sampletable
         SELECT random() * 10000
         FROM generate_series(1, 10000000);

执行并行查询

set max_parallel_workers_per_gather=2;
explain  analyze SELECT * FROM sampletable xx33xx  WHERE x < 423;
                                                                  QUERY PLAN                                                                  
----------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..197473.27 rows=412972 width=11) (actual time=3.280..5196.528 rows=423548 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on sampletable xx33xx  (cost=0.00..155176.07 rows=172072 width=11) (actual time=0.858..3833.489 rows=141183 loops=3)
         Filter: (x < '423'::numeric)
         Rows Removed by Filter: 3192151
 Planning time: 0.198 ms
 Execution time: 5217.594 ms
(8 rows)

查看统计信息

--结果显示blk_read_time>total_time
select query,total_time,blk_read_time,blk_write_time,queryid from pg_stat_statements where query like '%xx33xx%';
                              query                               | total_time  | blk_read_time | blk_write_time |  queryid  
------------------------------------------------------------------+-------------+---------------+----------------+-----------
 explain  analyze SELECT * FROM sampletable xx33xx  WHERE x < 423 | 5217.881478 |   7398.202573 |              0 | 403994828
(1 row)

清理缓存

[root@node1 ~]# sync
[root@node1 ~]# echo 3 >/proc/sys/vm/drop_caches 

执行非并行查询

set max_parallel_workers_per_gather=0;
explain  analyze SELECT * FROM sampletable xx22xx WHERE x < 423;
                                                          QUERY PLAN                                                           
-------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on sampletable xx22xx  (cost=0.00..228092.36 rows=412972 width=11) (actual time=1.265..5021.838 rows=423548 loops=1)
   Filter: (x < '423'::numeric)
   Rows Removed by Filter: 9576452
 Planning time: 0.213 ms
 Execution time: 5043.451 ms
(5 rows)

查看统计信息

select calls,query,total_time,blk_read_time,blk_write_time,queryid from pg_stat_statements where query like '%xx22xx%';
 calls |                              query                              | total_time  | blk_read_time | blk_write_time |  queryid  
-------+-----------------------------------------------------------------+-------------+---------------+----------------+-----------
     1 | explain  analyze SELECT * FROM sampletable xx22xx WHERE x < 423 | 5043.752919 |   2171.352884 |              0 | 512530458
(1 row)

查看物理读

--物理读的块数都是一样的
select * from pg_statio_all_tables where relname='sampletable';

实验结果

证明postgresql的pg_stat_statements的blk_read_time 在并行情况下统计的时间远高于没并行的情况，但是其实读取的块数是一样的，说明这个指标在并行情况下并不准确。