PostgreSQL的执行计划分析

Stella981
• 阅读 1154

近期有人提出想查看Postgresql的执行计划,下面分析下PG执行计划中的cost等相关值是怎么计算出来的:
PG的版本是9.1.2
 
1.终端工具PGADMIN,对执行的语句按F7即可,然后看数据输出和解释
PostgreSQL的执行计划分析
2.命令行分析:explain select * from table_name;

一般我们会比较关注消耗值cost和扫描的方式,如走索引或者full scan全表扫描.当COST值消耗比较大时需要注意是否有优化的可能。
与执行计划相关的几个参数,参看下面的示例:

kenyon=# select count(1) from dba.website ;                    --普通堆栈表,无任何索引约束
 count
-------
    20
(1 row)

kenyon=# explain select * from dba.website ;
                       QUERY PLAN                      
--------------------------------------------------------
 Seq Scan on website  (cost=0.00..1.20 rows=20 width=4)
(1 row)

 --relpages磁盘页,reltuples是行数(与实际不一定相符,一般略小)
kenyon=# select relpages,reltuples from pg_class where relname = 'website'; 
 relpages | reltuples
----------+-----------
        1 |        20
(1 row)

kenyon=# select 1*1+20*0.01;                                                                    
--cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost
 ?column?
----------
     1.20
(1 row)

kenyon=# show cpu_tuple_cost ;
 cpu_tuple_cost
----------------
 0.01
(1 row)

kenyon=# show seq_page_cost;
 seq_page_cost
---------------
 1
(1 row)


--加限制条件的执行计划

kenyon=# select count(1) from dba.website where hits >15;
 count
-------
     5
(1 row)

kenyon=# explain select * from dba.website where hits >15;
                      QUERY PLAN                      
-------------------------------------------------------
 Seq Scan on website  (cost=0.00..1.25 rows=5 width=4)
   Filter: (hits > 15)
(2 rows)

kenyon=# show cpu_operator_cost ;
 cpu_operator_cost
-------------------
 0.0025
(1 row)

因为扫描的总数是20行,不变的,所以COST不会下降,相反反而增加了0.05,这是因为额外消耗了CPU的时间去检查符合约束条件数据,即cost 在原来的基础上再增加 20 * 0.0025 = 0.05  (reltuples * cpu_operator_cost)


--加索引的执行计划
kenyon=# select count(1) from dba.website_2 ;
 count
-------
  8000
(1 row)

kenyon=# explain select * from dba.website_2 ;
                          QUERY PLAN                         
--------------------------------------------------------------
 Seq Scan on website_2  (cost=0.00..112.00 rows=8000 width=4)
(1 row)

kenyon=# select relpages,reltuples from pg_class where relname = 'website_2';
 relpages | reltuples
----------+-----------
       32 |      8000
(1 row)

kenyon=# explain select * from dba.website_2 where hits >7900;  --走的索引
                                    QUERY PLAN                                   
----------------------------------------------------------------------------------
 Index Scan using ind_website_2 on website_2  (cost=0.00..10.00 rows=100 width=4)
   Index Cond: (hits > 7900)
(2 rows)
()
kenyon=# explain select * from dba.website_2 where hits >10;    --未走索引(不满足索引条件,full scan)
                          QUERY PLAN                         
--------------------------------------------------------------
 Seq Scan on website_2  (cost=0.00..132.00 rows=7991 width=4)   -- 132 = 112+8000*0.0025
   Filter: (hits > 10)
(2 rows)


虽然读取的COST更大,但是因为索引的缘故,访问的数据量变小了,所以总体COST是下降的。

--多表JOIN的执行计划 示例: 若想看实际的一个执行时间,可以加上 analyze 参数

kenyon=# explain analyze select * from dba.website a ,dba.website_2 b where a.hits = b.hits and a.hits >18; 
                                             QUERY PLAN 
--------------------------------------------------------------------------------------------------------------------------------------- 
Merge Join (cost=1.26..1.90 rows=2 width=8) (actual time=0.070..0.075 rows=2 loops=1) 
  Merge Cond: (b.hits = a.hits) 
  -> Index Scan using ind_website_2 on website_2 b (cost=0.00..235.25 rows=8000 width=4) (actual time=0.013..0.020 rows=21 loops=1) 
  -> Sort (cost=1.26..1.26 rows=2 width=4) (actual time=0.035..0.037 rows=2 loops=1) 
     Sort Key: a.hits 
     Sort Method: quicksort Memory: 17kB 
     -> Seq Scan on website a (cost=0.00..1.25 rows=2 width=4) (actual time=0.009..0.011 rows=2 loops=1) 
      Filter: (hits > 18) 
Total runtime : 0.120 ms 
(9 rows)

total runtime 是执行器启动和关闭的时间,但不包括解析,重写和规划的时间
注意: pg_class中的relpages,reltuples数据不是实时更新的,一般在vacuum analyze和少部分DDL(如建立索引)后更新。
示例1:

kenyon=# insert into dba.website select generate_series(8000,9000);
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
 relpages | reltuples |    relname    | relkind
----------+-----------+---------------+---------
        1 |        20 | website       | r
       32 |      8000 | website_2     | r
       20 |      8000 | ind_website_2 | i
(3 rows)

kenyon=# vacuum analyze dba.website;
VACUUM
kenyon=# vacuum analyze dba.website;
VACUUM
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
 relpages | reltuples |    relname    | relkind
----------+-----------+---------------+---------
        5 |      1021 | website       | r
       36 |      8999 | website_2     | r
       22 |      8999 | ind_website_2 | i
(3 rows)

示例2:

kenyon=# insert into dba.website select generate_series(8000,9000);
INSERT 0 1001
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
 relpages | reltuples |    relname    | relkind
----------+-----------+---------------+---------
        1 |        21 | website       | r
       36 |      8999 | website_2     | r
       22 |      8999 | ind_website_2 | i
(3 rows)

kenyon=# create index ind_website on dba.website(hits);
CREATE INDEX
kenyon=# select relpages,reltuples,relname,relkind from pg_class where relname like '%website%';
 relpages | reltuples |    relname    | relkind
----------+-----------+---------------+---------
        5 |      1022 | website       | r
       36 |      8999 | website_2     | r
       22 |      8999 | ind_website_2 | i
        5 |      1022 | ind_website   | i
(4 rows)

所涉及的系统表:
pg_stats
pg_statistic
pg_class
pg_stat是任何人都可以看的,而且可读性高,比较直观,pg_statistic只有superuser才能读,并且可读性差,普通人员建议看pg_stats,pg_stats是pg_statistic的视图。 这两个表也不是实时更新的,需要vacuum analyze时会更新
所涉及的系统变量:
default_statistics_target
geqo_threshold
join_collapse_limit
from_collapse_limit

kenyon=# show default_statistics_target ;
 default_statistics_target
---------------------------
 100
(1 row)

kenyon=# show geqo_threshold ;         --这个参数的大小会设置执行计划从穷举搜索到概率选择性搜索的临界值
 geqo_threshold
----------------
 12
(1 row)

kenyon=# show join_collapse_limit ;    --join连接走执行计划上限
 join_collapse_limit
---------------------
 8
(1 row)

kenyon=# show from_collapse_limit ;
 from_collapse_limit
---------------------
 8
(1 row)

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 }

例子:

kenyon=# explain (analyze,verbose,costs,buffers) select id from dba.test222 order by id desc limit 1;
                                                          QUERY PLAN                                                         
------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=1807.80..1807.80 rows=1 width=4) (actual time=87.167..87.168 rows=1 loops=1)
   Output: id
   Buffers: shared hit=393
   ->  Sort  (cost=1807.80..2043.60 rows=94320 width=4) (actual time=87.165..87.165 rows=1 loops=1)
         Output: id
         Sort Key: test222.id
         Sort Method: top-N heapsort  Memory: 17kB
         Buffers: shared hit=393
         ->  Seq Scan on dba.test222  (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.036..42.847 rows=100000 loops=1)
               Output: id
               Buffers: shared hit=393
 Total runtime: 87.183 ms
(12 rows)

kenyon=# explain (analyze,verbose,costs,buffers) select max(id) from dba.test222;
                                                       QUERY PLAN                                                      
------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1572.00..1572.01 rows=1 width=4) (actual time=77.679..77.680 rows=1 loops=1)
   Output: max(id)
   Buffers: shared hit=393
   ->  Seq Scan on dba.test222  (cost=0.00..1336.20 rows=94320 width=4) (actual time=0.012..36.908 rows=100000 loops=1)
         Output: id
         Buffers: shared hit=393
 Total runtime: 77.701 ms
(7 rows)

explain参数解释:
ANALYZE :执行命令并显示执行事件,默认false
VERBOSE :对执行计划提供额外的信息,如查询字段信息等,默认false
COSTS :显示执行计划的,默认true
BUFFERS :默认false,前置条件是analyze
FORMAT :默认格式是text

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Wesley13 Wesley13
2年前
java将前端的json数组字符串转换为列表
记录下在前端通过ajax提交了一个json数组的字符串,在后端如何转换为列表。前端数据转化与请求varcontracts{id:'1',name:'yanggb合同1'},{id:'2',name:'yanggb合同2'},{id:'3',name:'yang
Jacquelyn38 Jacquelyn38
2年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Stella981 Stella981
2年前
Python之time模块的时间戳、时间字符串格式化与转换
Python处理时间和时间戳的内置模块就有time,和datetime两个,本文先说time模块。关于时间戳的几个概念时间戳,根据1970年1月1日00:00:00开始按秒计算的偏移量。时间元组(struct_time),包含9个元素。 time.struct_time(tm_y
Wesley13 Wesley13
2年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
2年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
2年前
Mysql 执行计划各列释义
在排查编写Mysql查询语句时,除了需要满足业务条件,还需要考虑所编写SQL的性能表现,避免出现慢SQL导致大量慢查询的情况。通常,可以通过查看执行计划的方式查看所编写SQL语句的性能优劣。此外,还可以通过查看语句的分阶段执行的时间、操作消耗来进行补充分析。1\.执行计划的列1.1.id列查询的序号1.2.s
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
4个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这