<< 返回文章列表 使用EXPLAIN将显示SQL语句的执行计划，可以看到表会采用什么样的扫描方式，如：简单的顺序扫描、索引扫描等。如果引用了多个表，执行计划还会显示用到的JOIN算法。
执行计划的最关键的部分是语句的预计执行开销，这是计划生成器估算执行该语句将花费多长的时间。
若指定了ANALYZE选项，则该语句会被执行，然后根据实际的运行结果显示统计数据，包括每个计划节点内时间总开销（毫秒为单位）和实际返回的总行数。这对于判断计划生成器的估计是否接近现实非常有用。
显示不同详细程度的执行计划信息，比较常见的有如下几种：
为了测量运行时在执行计划中每个节点的开销，EXPLAIN ANALYZE或EXPLAIN PERFORMANCE会在当前查询执行上增加性能分析的开销。在一个查询上运行EXPLAIN ANALYZE或EXPLAIN PERFORMANCE有时会比普通查询明显的花费更多的时间。超支的数量依赖于查询的本质和使用的平台。
因此，当定位SQL运行慢问题时，如果SQL长时间运行未结束，建议通过EXPLAIN命令查看执行计划，进行初步定位。如果SQL可以运行出来，则推荐使用EXPLAIN ANALYZE或EXPLAIN PERFORMANCE查看执行计划及其实际的运行信息，以便更精准地定位问题原因。
在指定ANALYZE/PERFORMANCE选项时，语句会被执行。如果用户想使用EXPLAIN分析INSERT、UPDATE、DELETE、CREATE TABLE AS或EXECUTE语句，而不想改动数据（执行这些语句会影响数据），请使用如下方法：
测试数据准备完成。
查看select语句的执行计划，对数据没有影响。
DML语句查看执行计划，如果不需要数据变化，注意在查看执行计划之前需要加上begin,查看完成后再执行rollback.
如果不需要数据发生变化，查看执行计划之前需要加begin，查看完成后再执行rollback，数据不会发生变化。
该测试我们直观看到了，查看执行计划时指定ANALYZE或PERFORMANCE选项时，语句会被执行，DML语句会导致数据发生变化，不加ANALYZE或PERFORMANCE选项时，仅显示执行计划，DML语句不会导致数据变化。如果不需要数据发生变化，查看执行计划之前需要加"begin;"或者"start transaction;"，查看完成后再执行"rollback;"，数据不会发生变化。
MogDB 数据库查看执行计划需要注意的要点
2024年7月30日
杨天成
27
EXPLAIN用法
-
EXPLAIN statement：只生成执行计划，不实际执行。其中statement代表SQL语句。
-
EXPLAIN ANALYZE statement：生成执行计划，进行执行，并显示执行的概要信息。显示中加入了实际的运行时间统计，包括在每个规划节点内部花掉的总时间（以毫秒计）和它实际返回的行数。
-
EXPLAIN PERFORMANCE statement：生成执行计划，进行执行，并显示执行期间的全部信息。
查看执行计划需要注意
START TRANSACTION;
EXPLAIN ANALYZE ...;
ROLLBACK;
下面进行测试，测试情况如下：
准备测试数据
MogDB=# create table tctest(
MogDB(# id int ,
MogDB(# info varchar(500),
MogDB(# val_int int,
MogDB(# val_float decimal(12,2),
MogDB(# crt_date date,
MogDB(# crt_time timestamp,
MogDB(# remark char(50)
MogDB(# )
MogDB-# WITH (autovacuum_enabled = off, toast.autovacuum_enabled = off,compression=no);
CREATE TABLE
MogDB=#
MogDB=#
MogDB=# alter table tctest add constraint tctest_pkey primary key (id);
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tctest_pkey" for table "tctest"
ALTER TABLE
MogDB=# create index idx1_tctest on tctest (crt_date);
CREATE INDEX
MogDB=# create index idx2_tctest on tctest (info);
CREATE INDEX
MogDB=#
MogDB=#
MogDB=# insert into tctest(id,info,val_int,val_float,crt_date,crt_time,remark)
MogDB-# select
MogDB-# generate_series(1,1999999999999999) id,
MogDB-# md5(random()::text) info,
MogDB-# generate_series(100,1999999999999999) val_int,
MogDB-# random()*(10^5) val_float,
MogDB-# sysdate crt_date,
MogDB-# now() crt_time,
MogDB-# 'first'||(random()*(10^3))::integer remark
MogDB-# limit 1500000;
INSERT 0 1500000
MogDB=#
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 100 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 101 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 102 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 103 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 104 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 105 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 106 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 107 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 108 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
MogDB=#
查看执行计划
-
只生成执行计划，不实际执行
MogDB=# explain select * from tctest where id<10;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tctest_pkey on tctest (cost=0.00..8.44 rows=11 width=116)
Index Cond: (id < 10)
(2 rows)
-
生成执行计划，且实际执行
MogDB=# explain analyze select * from tctest where id<10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Index Scan using tctest_pkey on tctest (cost=0.00..8.44 rows=11 width=116) (actual time=0.007..0.011 rows=9 loops=1)
Index Cond: (id < 10)
Total runtime: 0.084 ms
(3 rows)
-
生成执行计划，且实际执行
MogDB=# explain performance select * from tctest where id<10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Index Scan using tctest_pkey on public.tctest (cost=0.00..8.44 rows=11 width=116) (actual time=0.007..0.009 rows=9 loops=1)
Output: id, info, val_int, val_float, crt_date, crt_time, remark
Index Cond: (tctest.id < 10)
(Buffers: shared hit=4)
(CPU: ex c/r=4043733957297190, ex row=9, ex cyc=36393605615674712, inc cyc=36393605615674712)
Total runtime: 0.072 ms
(6 rows)
-
仅显示执行计划，数据没有发生变化。
MogDB=# explain update tctest set val_int=100 where id<10;
QUERY PLAN
-----------------------------------------------------------------------------------
Update on tctest (cost=0.00..8.44 rows=11 width=118)
-> Index Scan using tctest_pkey on tctest (cost=0.00..8.44 rows=11 width=118)
Index Cond: (id < 10)
(3 rows)
-
查看执行计划的数据，数据没有发生变化。
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 100 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 101 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 102 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 103 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 104 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 105 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 106 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 107 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 108 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
-
查看执行计划，实际执行了该语句
MogDB=# explain analyze update tctest set val_int=100 where id<10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Update on tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.251..0.571 rows=9 loops=1)
-> Index Scan using tctest_pkey on tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.011..0.027 rows=9 loops=1)
Index Cond: (id < 10)
Total runtime: 0.681 ms
(4 rows)
-
analyze 数据发生了变化。
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 100 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 100 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 100 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 100 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 100 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 100 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 100 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 100 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 100 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
-
查看执行计划，实际执行了该语句
MogDB=# explain performance update tctest set val_int=200 where id<10;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Update on public.tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.081..0.180 rows=9 loops=1)
(Buffers: shared hit=30 dirtied=3)
(CPU: ex c/r=-3639725859365662, ex row=9, ex cyc=-32757532734290960, inc cyc=3639725859461305)
-> Index Scan using tctest_pkey on public.tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.013..0.068 rows=9 loops=1)
Output: id, info, 200, val_float, crt_date, crt_time, remark, ctid
Index Cond: (tctest.id < 10)
(Buffers: shared hit=21 dirtied=2)
(CPU: ex c/r=4044139843750251, ex row=9, ex cyc=36397258593752264, inc cyc=36397258593752264)
Total runtime: 0.298 ms
(9 rows)
-
performance 数据发生了变化
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 200 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 200 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 200 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 200 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 200 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 200 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 200 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 200 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 200 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
MogDB=#
MogDB=# begin;
BEGIN
MogDB=# explain analyze update tctest set val_int=10000 where id<10;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Update on tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.074..0.122 rows=9 loops=1)
-> Index Scan using tctest_pkey on tctest (cost=0.00..8.44 rows=11 width=118) (actual time=0.012..0.021 rows=9 loops=1)
Index Cond: (id < 10)
Total runtime: 0.260 ms
(4 rows)
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 10000 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 10000 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 10000 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 10000 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 10000 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 10000 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 10000 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 10000 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 10000 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
MogDB=#
MogDB=# rollback;
ROLLBACK
MogDB=#
MogDB=# select * from tctest where id<10;
id | info | val_int | val_float | crt_date | crt_time | remark
----+----------------------------------+---------+-----------+---------------------+----------------------------+----------------------------------------------------
1 | 477323c19c9d523e012bbe0282dfc8be | 200 | 18235.59 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first739
2 | cf025a0ff205f620948cfbf8e510e3a1 | 200 | 31577.50 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first323
3 | 2f155efb19290c3d5821cd35655f0ec6 | 200 | 14517.16 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first42
4 | 279f92370d522fe69debd07ced9fa1d6 | 200 | 22667.34 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first506
5 | c0606cc2c0ce4fdea05597669e386fb2 | 200 | 2745.43 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first239
6 | a0d552ef5232d6f36d66058b393db78f | 200 | 44501.53 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first403
7 | 91e7303fa8d9bf50c7131aa183059456 | 200 | 50785.08 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first804
8 | 37b1d048d5b4c4f4a798145cedbdbf0e | 200 | 89768.87 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first141
9 | a7a98ce07ffa883d77c017e6f8ff8f0e | 200 | 94710.54 | 2024-04-26 15:22:10 | 2024-04-26 15:22:10.080794 | first912
(9 rows)
小结