PostgreSQL执行计划获取与修改

基础操作之___执行计划获取与修改

说明：

• explain + sql：只显示执行计划，不实际执行
• explain analyze + sql：实际执行，并显示执行计划

1、查询sql执行计划

启用sql执行时间

htdb=# \timing
Timing is on.

（1）查询执行计划（不执行）

htdb=# explain select count(1) from httab;
                           QUERY PLAN                            
-----------------------------------------------------------------
 Aggregate  (cost=219.00..219.01 rows=1 width=8)
   ->  Seq Scan on httab  (cost=0.00..194.00 rows=10000 width=0)
(2 rows)

Time: 8.421 ms

（2）查询执行计划（实际执行）

htdb=# explain analyze select count(1) from httab;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=219.00..219.01 rows=1 width=8) (actual time=2.767..2.768 rows=1 loops=1)
   ->  Seq Scan on httab  (cost=0.00..194.00 rows=10000 width=0) (actual time=0.747..2.450 rows=10000 loops=1)
 Planning Time: 0.041 ms
 Execution Time: 2.790 ms
(4 rows)

Time: 3.073 ms

通过用时，和统计信息两种方式，都能看出二者差别

2、获取执行计划IO信息

使用buffers参数显示sql执行时IO相关信息

htdb=# explain (analyze,buffers) select count(1) from httab;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=219.00..219.01 rows=1 width=8) (actual time=0.929..0.929 rows=1 loops=1)
   Buffers: shared hit=94
   ->  Seq Scan on httab  (cost=0.00..194.00 rows=10000 width=0) (actual time=0.006..0.553 rows=10000 loops=1)
         Buffers: shared hit=94
 Planning Time: 0.041 ms
 Execution Time: 0.945 ms
(6 rows)

Time: 1.310 ms
htdb=# 

注意：explain后跟多个选项时，需要加括号

hit表示命中缓存，重启后缓存消失，所以变成物理读（read），如下：

htdb=# \! pg_ctl restart -D /pgdata12
waiting for server to shut down.... done
server stopped
waiting for server to start....2022-08-25 15:40:50.392 CST [10128] LOG:  starting PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
2022-08-25 15:40:50.393 CST [10128] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-08-25 15:40:50.393 CST [10128] LOG:  listening on IPv6 address "::", port 5432
2022-08-25 15:40:50.393 CST [10128] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-08-25 15:40:50.404 CST [10128] LOG:  redirecting log output to logging collector process
2022-08-25 15:40:50.404 CST [10128] HINT:  Future log output will appear in directory "log".
 done
server started
htdb=# \c
You are now connected to database "htdb" as user "htuser".
htdb=# 
htdb=# explain (analyze,buffers) select count(1) from httab;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=219.00..219.01 rows=1 width=8) (actual time=2.696..2.696 rows=1 loops=1)
   Buffers: shared read=94
   ->  Seq Scan on httab  (cost=0.00..194.00 rows=10000 width=0) (actual time=0.006..2.246 rows=10000 loops=1)
         Buffers: shared read=94
 Planning Time: 0.255 ms
 Execution Time: 2.739 ms
(6 rows)

Time: 3.804 ms
htdb=#

3、修改SQL执行计划

pg中通过修改执行计划选项开关来修改执行计划

（1）查询表（无主键索引）

没有主键，走的全表扫描

htdb=# explain (analyze,buffers) select * from httab where id =998;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on httab  (cost=0.00..219.00 rows=1 width=41) (actual time=0.050..0.456 rows=1 loops=1)
   Filter: (id = 998)
   Rows Removed by Filter: 9999
   Buffers: shared hit=94
 Planning Time: 0.053 ms
 Execution Time: 0.464 ms
(6 rows)

（2）查询表（有主键索引）

指定索引列条件，执行计划走index scan

htdb=# ALTER TABLE httab ADD CONSTRAINT httab_pkey PRIMARY KEY (id); 
ALTER TABLE

htdb=#  explain (analyze,buffers) select * from httab where id =998;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Index Scan using httab_pkey on httab  (cost=0.29..8.30 rows=1 width=41) (actual time=0.445..0.447 rows=1 loops=1)
   Index Cond: (id = 998)
   Buffers: shared hit=1 read=2
 Planning Time: 0.824 ms
 Execution Time: 0.461 ms
(5 rows)

Time: 1.623 ms
htdb=#

（3）enable_indexscan参数

禁用enable_indexscan后，查询走bitmap heap scan

htdb=# set enable_indexscan = off;
SET
htdb=# explain (analyze,buffers) select * from httab where id =998;
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on httab  (cost=4.29..8.31 rows=1 width=41) (actual time=0.009..0.010 rows=1 loops=1)
   Recheck Cond: (id = 998)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=2
   ->  Bitmap Index Scan on httab_pkey  (cost=0.00..4.29 rows=1 width=0) (actual time=0.007..0.007 rows=1 loops=1)
         Index Cond: (id = 998)
         Buffers: shared read=2
 Planning Time: 0.164 ms
 Execution Time: 0.021 ms
(9 rows)

htdb=# 

（4）enable_bitmapscan参数

禁用enable_bitmapscan后，查询再次走全表扫描

htdb=# explain (analyze,buffers) select * from httab where id =998;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on httab  (cost=0.00..219.00 rows=1 width=41) (actual time=0.055..0.463 rows=1 loops=1)
   Filter: (id = 998)
   Rows Removed by Filter: 9999
   Buffers: shared hit=94
 Planning Time: 0.045 ms
 Execution Time: 0.472 ms
(6 rows)

auto_explain插件，能够把执行时间较长的SQL及执行计划写到PG日志里

查看执行计划的网站：https://explain.depesz.com/