« AWR 报告解读：Time Model Statistics 信息的计算和获取 | Blog首页 | PostgreSQL 基础：行列转换实现类MySQL的 group_concat 功能 »

PostgreSQL 基础：如何查看 PostgreSQL 中SQL的执行计划

在任何数据库中，分析和优化SQL的执行，最重要的工作就是执行计划的解读，在 PostgreSQL 中，执行计划的获取和 Oracle 数据库非常类似。

Explain 子句可以帮助我们展示和分析执行计划。

其语法如下：

EXPLAIN
Name
EXPLAIN-- show the execution plan of a statement
Synopsis
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ]
VERBOSE [ boolean ]
COSTS [ boolean ]
BUFFERS [ boolean ]
FORMAT { TEXT | XML | JSON | YAML }

最常用的选项是 ANALYZE ，通过这个参数会真正执行SQL，务必谨慎，真正执行SQL可以活动真实的执行计划。

缺省的 explain 只进行执行计划解析，并不执行。执行计划中的 cost 是成本，第一个数字代表本步骤成本，后一个数字代表的是总成本。执行计划解读，自底向上，自右向左。

enmotech=# explain select error_code,array_to_string(array_agg(db_version),',') from oracode where error_code='ORA-04031' group by error_code;

QUERY PLAN

----------------------------------------------------------------

GroupAggregate (cost=0.00..17.06 rows=3 width=90)

Group Key: error_code

-> Seq Scan on oracode (cost=0.00..17.00 rows=3 width=116)

Filter: ((error_code)::text = 'ORA-04031'::text)

在以上执行计划中，涉及4个关键字：

Fiter 指应用限定条件进行记录过滤；

Seq Scan 指表扫描，也即全表扫描；

Group Key 指分组查询的分组键值；

GroupAggregate 指分组聚合结果；

再看一个复杂一点的查询，这个查询涉及两个表，总成本是 45.80，对两个表进行了 Nested Loop 的嵌套循环处理：

enmotech=# explain select a.error_code,array_to_string(array_agg(db_version),',') from oracode a,pgcode where a.error_code='ORA-04031' group by a.error_code;

QUERY PLAN

------------------------------------------------------------------------------

GroupAggregate (cost=0.00..45.80 rows=3 width=90)

Group Key: a.error_code

-> Nested Loop (cost=0.00..41.26 rows=900 width=116)

-> Seq Scan on pgcode (cost=0.00..13.00 rows=300 width=0)

-> Materialize (cost=0.00..17.02 rows=3 width=116)

-> Seq Scan on oracode a (cost=0.00..17.00 rows=3 width=116)

Filter: ((error_code)::text = 'ORA-04031'::text)

我们可以用 Analyze 选项让这个语句真正执行，由于这两个表没有关联条件，也没有索引，所以执行计划是对两个表进行全表扫描，然后 NL ，事实上就是笛卡儿积：

enmotech=# explain analyze select a.error_code,array_to_string(array_agg(db_version),',') from oracode a,pgcode where a.error_code='ORA-04031' group by a.error_code;

QUERY PLAN

------------------------------------------------------------------------------------------------------------------------

GroupAggregate (cost=0.00..45.80 rows=3 width=90) (actual time=0.324..0.324 rows=1 loops=1)

Group Key: a.error_code

-> Nested Loop (cost=0.00..41.26 rows=900 width=116) (actual time=0.214..0.224 rows=6 loops=1)

-> Seq Scan on pgcode (cost=0.00..13.00 rows=300 width=0) (actual time=0.086..0.088 rows=3 loops=1)

-> Materialize (cost=0.00..17.02 rows=3 width=116) (actual time=0.036..0.038 rows=2 loops=3)

-> Seq Scan on oracode a (cost=0.00..17.00 rows=3 width=116) (actual time=0.047..0.049 rows=2 loops=1)

Filter: ((error_code)::text = 'ORA-04031'::text)

Planning Time: 0.280 ms

Execution Time: 2.387 ms

(9 rows)

两个测试表的数据是这样的：

enmotech=# select * from pgcode;

error_code | code_desc

------------+---------------------------------------

42000 | syntax_error_or_access_rule_violation

42501 | insufficient_privilege

42602 | invalid_name

(3 rows)

enmotech=# select * from oracode;

error_code | db_version

------------+------------

ORA-04031 | 11g

ORA-04031 | 19c

(2 rows)

单独查询和组合查询的结果如下：

enmotech=# select a.error_code,array_to_string(array_agg(db_version),',') from oracode a where a.error_code='ORA-04031' group by a.error_code;

error_code | array_to_string

------------+-----------------

ORA-04031 | 11g,19c

(1 row)

enmotech=# select a.error_code,array_to_string(array_agg(db_version),',') from oracode a,pgcode where a.error_code='ORA-04031' group by a.error_code;

error_code | array_to_string

------------+-------------------------

ORA-04031 | 11g,19c,11g,19c,11g,19c

(1 row)

一点一滴，从执行计划开始。

By eygle on 2019-09-16 10:04 | Comments (0) | FAQ | 3361 |

PostgreSQL