0

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

eygle 2019-09-12
577

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

ExplainCost.jpg

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)

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

「喜欢文章,快来给作者赞赏墨值吧」
1人已赞赏
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论

关注
最新发布
暂无内容,敬请期待...
数据库资讯
最新 热门 更多
本月热门
近期活动
全部
暂无活动,敬请期待...
相关课程
全部