PostgreSQL explain解析(一): buffers
对于oracle DBA来说在优化SQL时，有gather_plan_statistic hint或SQL Monitor Report可以显示每步所花费的时间和逻辑读、A-rows等，减少响应时间确实是追求的目标，有时一个相同的执行计划也可能响应时间相差很大，如大表做了大量的delete，记录只剩1行或空表时，全表扫描时间依旧很久，在Postgresql可观察性不如ORACLE,通常使用explain analyze优化SQL也会面临这个问题，这时buffers选项就是一个不错的选择，我相信每个需要做SQL优化工作的人都必须启用和使用它。一个好的优化工作流程最重要的组成部分之一是用于实验、执行计划分析和验证优化思想的环境。但是对于不同的环境，还原问题取决于许多因素:
1，hardware performance,
2， filesystem and OS, and their settings,
3，the state of the caches (file cache and Postgres buffer pool),
4，concurrent activity and locking.
5，etc..
通过扩展EXPLAIN添加值来描述postgresql数据库中SQL的执行计划每个操作读/写的数据,需要使用ANALYZE参数，从Postgres 13开始，它可以额外报告planner的缓冲区使用情况。默认情况下，BUFFERS是关闭的。在更多的情况下，使用BUFFERS将极大地帮助我们理解内部发生了什么，这样我们就可以在查询优化方面做出良好的决定:
1，high level of table/index bloat,
2，very wide table (significant TOAST size),
3，HOT updates vs. index amplification.
shared blocks (hit plus read)与处理行(包括任何过滤掉的行)的比率也很有趣:高比率意味着Postgres正在为它正在做的事情读取大量数据，这可能是膨胀的迹象。
先简单看一下buffers 输出
[local]:5432 postgres@anbob=# explain select * from demo1 order by 1;
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using demo1_n on demo1 (cost=0.29..1702.29 rows=10000 width=1012)
(1 row)
[local]:5432 postgres@anbob=# explain (analyze) select * from demo1 order by 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using demo1_n on demo1 (cost=0.29..1702.29 rows=10000 width=1012) (actual time=8.725..141.887 rows=10000 loops=1)
Planning Time: 0.056 ms
Execution Time: 142.397 ms
(3 rows)
[local]:5432 postgres@anbob=# explain (analyze, buffers) select * from demo1 order by 1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Index Scan using demo1_n on demo1 (cost=0.29..1702.29 rows=10000 width=1012) (actual time=0.015..2.925 rows=10000 loops=1)
Buffers: shared hit=1458
Planning Time: 0.058 ms
Execution Time: 3.532 ms
(4 rows)
[local]:5432 postgres@anbob=# explain (analyze, buffers, costs off) select * from demo1 order by 1;
QUERY PLAN
---------------------------------------------------------------------------------
Index Scan using demo1_n on demo1 (actual time=0.015..3.133 rows=10000 loops=1)
Buffers: shared hit=1458
Planning Time: 0.055 ms
Execution Time: 4.988 ms
(4 rows)
[local]:5432 postgres@anbob=# \d demo1
Table "public.demo1"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
n | integer | | |
a | integer | | |
x | text | | |
Indexes:
"demo1_n" UNIQUE, btree (n)
[local]:5432 postgres@anbob=# select count(*) from demo1;
count
-------
10000
(1 row)
[local]:5432 postgres@anbob=# select oid,relname,relpages,reltuples from pg_class where relname='demo1';
oid | relname | relpages | reltuples
-------+---------+----------+-----------
27479 | demo1 | 1429 | 10000
(1 row)
[local]:5432 postgres@anbob=# select oid,relname,relpages,reltuples from pg_class where relname='demo1_n';
oid | relname | relpages | reltuples
-------+---------+----------+-----------
27485 | demo1_n | 30 | 10000
(1 row)
[postgres@oel7db1 ~]$ sh show block
block_size | 8192 | Shows the size of a disk block.
wal_block_size | 8192 | Shows the block size in the write ahead log.
Note:
看到上面的查询使用上了索引和回表Index Scan（不是Index Only Scan）, 产生了1458个buffer blocks, 默认的block大小8K，1458*8/1024约11M的cache读。
[local]:5432 postgres@anbob=# explain (analyze, buffers, costs off) select * from demo1 order by a;
QUERY PLAN
-------------------------------------------------------------------------------------
Gather Merge (actual time=25.488..31.866 rows=10000 loops=1)
Workers Planned: 1
Workers Launched: 1
Buffers: shared hit=1486, temp read=1255 written=1260
-> Sort (actual time=10.379..12.932 rows=5000 loops=2)
Sort Key: a
Sort Method: external merge Disk: 10040kB
Buffers: shared hit=1486, temp read=1255 written=1260
Worker 0: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on demo1 (actual time=0.003..0.865 rows=5000 loops=2)
Buffers: shared hit=1429
Planning Time: 0.058 ms
Execution Time: 35.773 ms
(13 rows)
Note:
Shared Hit在缓存中找到的来自索引/表的块数。这包括子操作的值，并且是一个总价值(即不是每个循环)。上面的查询使用的Parallel Seq Scan产生了Buffers 1429（表heap全部block）。反馈给排序后产生了1486 block（产生部分外部写）.
在优化查询时，暂时忘记TIMING而使用buffers.只有在开始和完成优化过程时才返回计时数字。在流程内部，重点关注计划结构和buffers数量。是的，SQL优化的最终目标是拥有尽可能低的TIMING数。但在大多数情况下，这是通过减少所涉及的数据量来实现的——减少buffer数量。
相同的执行计划
以前在Oracle里分享过Oracle 12C新特性: Attribute Clustering 特性，通过一个索引来降低cluster factor有序存放，减少IO数，在Postgresql中可以使用cluster命令。
[local]:5432 postgres@anbob=# create table t1 as
anbob-# select id::int8, round(random() * 100000)::int8 as num
anbob-# from generate_series(1, 10000000) as id;
SELECT 10000000
[local]:5432 postgres@anbob=#
[local]:5432 postgres@anbob=# create table t2 as select * from t1;
SELECT 10000000
[local]:5432 postgres@anbob=# alter table t2 add primary key (id);
ALTER TABLE
[local]:5432 postgres@anbob=# create index i_t2_num on t2 using btree (num);
CREATE INDEX
[local]:5432 postgres@anbob=# explain (analyze, buffers) select * from t1 where num > 10000 order by num limit 1000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=312899.55..313016.22 rows=1000 width=16) (actual time=2191.244..2191.783 rows=1000 loops=1)
Buffers: shared hit=2322 read=51847
-> Gather Merge (cost=312899.55..1188613.79 rows=7505598 width=16) (actual time=2191.242..2191.699 rows=1000 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2322 read=51847
-> Sort (cost=311899.53..321281.52 rows=3752799 width=16) (actual time=2173.342..2173.425 rows=769 loops=3)
Sort Key: num
Sort Method: top-N heapsort Memory: 129kB
Buffers: shared hit=2322 read=51847
Worker 0: Sort Method: top-N heapsort Memory: 128kB
Worker 1: Sort Method: top-N heapsort Memory: 129kB
-> Parallel Seq Scan on t1 (cost=0.00..106137.60 rows=3752799 width=16) (actual time=0.040..1410.930 rows=3000029 loops=3)
Filter: (num > 10000)
Rows Removed by Filter: 333304
Buffers: shared hit=2208 read=51847
Planning:
Buffers: shared hit=10
Planning Time: 0.111 ms
Execution Time: 2191.851 ms
(20 rows)
[local]:5432 postgres@anbob=# explain (analyze, buffers) select * from t2 where num > 10000 order by num limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..45.47 rows=1000 width=16) (actual time=0.019..0.797 rows=1000 loops=1)
Buffers: shared hit=1003
-> Index Scan using i_t2_num on t2 (cost=0.43..404505.88 rows=8981915 width=16) (actual time=0.018..0.704 rows=1000 loops=1)
Index Cond: (num > 10000)
Buffers: shared hit=1003
Planning Time: 0.085 ms
Execution Time: 0.865 ms
(7 rows)
[local]:5432 postgres@anbob=# \d+t t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
id | bigint | | not null | | plain | |
num | bigint | | | | plain | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (id)
"i_t2_num" btree (num)
Access method: heap
[local]:5432 postgres@anbob=# cluster t2 using i_t2_num;
CLUSTER
[local]:5432 postgres@anbob=# explain (analyze, buffers) select * from t2 where num > 10000 order by num limit 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.43..45.47 rows=1000 width=16) (actual time=0.018..0.270 rows=1000 loops=1)
Buffers: shared hit=11
-> Index Scan using i_t2_num on t2 (cost=0.43..404508.08 rows=8982041 width=16) (actual time=0.017..0.178 rows=1000 loops=1)
Index Cond: (num > 10000)
Buffers: shared hit=11
Planning Time: 0.086 ms
Execution Time: 0.340 ms
(7 rows)
[local]:5432 postgres@anbob=# \d+t t2
Table "public.t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+--------+-----------+----------+---------+---------+--------------+-------------
id | bigint | | not null | | plain | |
num | bigint | | | | plain | |
Indexes:
"t2_pkey" PRIMARY KEY, btree (id)
"i_t2_num" btree (num) CLUSTER
Access method: heap
Note:
从上面的查询Parallel Seq Scan和Index Scan using i_t2_num 比较好区分，2191.851 ms到0.865 ms， 但是在cluster前后同样的执行计划从0.865 ms到0.340 ms 如果没有buffers选项就不太容易比较出不同，因为cluster更有序的数据，才产生buffer blocks 1003降到11, 所以都有响应时间上的提升， 可见buffers的重要性。
日常小技巧
因为目前版本explain 没有增加buffers，建议每次都使用buffers，可以配置个别名，减少日常交互输入。也可以增到~/.psqlrc文件中。
[local]:5432 postgres@anbob=# \set eab EXPLAIN (ANALYZE, BUFFERS)
[local]:5432 postgres@anbob=# :eab select 1;
QUERY PLAN
------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)
Planning Time: 0.017 ms
Execution Time: 0.013 ms
(3 rows)
[local]:5432 postgres@anbob=# :eab select * from demo1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Seq Scan on demo1 (cost=0.00..1529.00 rows=10000 width=1012) (actual time=0.009..1.589 rows=10000 loops=1)
Buffers: shared hit=1429
Planning Time: 0.051 ms
Execution Time: 2.210 ms
(4 rows)
当发现表buffers较高，怀疑存在较大的膨胀时，可以使用vacuum verbose，过程中会提示可能的原因，导致VACUUM不能清除死元组。有时是因为我们有一个长时间运行的事务。
总结
总是使用EXPLAIN (ANALYZE, BUFFERS)，而不仅仅是EXPLAIN ANALYZE——这样你就可以看到Postgres在执行查询时所做的实际IO工作。这样可以更好地理解所涉及的数据量。如果您开始将缓冲区数字转换为字节——只需将它们乘以块大小(大多数情况下为8 KiB)，那就更好阅读。
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。