PostgreSQL执行计划的解释

ゞ浴缸里的玫瑰 2022-08-24 04:19 126阅读 0赞

[http://blog.itpub.net/24484596/viewspace-730958/][http_blog.itpub.net_24484596_viewspace-730958]

一个顺序磁盘页面操作的cost值由系统参数seq\_page\_cost (floating point)参数指定的，由于这个参数默认为1.0，所以我们可以认为一次顺序磁盘页面操作的cost值为1。  
下面  
osdba=\# explain select \* from t;  
QUERY PLAN  
———————————————————-  
Seq Scan on t  (cost=0.00 ..4621.00  rows=300000  width=10 )  
(1 row)

cost=说明：

*  第一个数字0.00表示启动cost，这是执行到返回第一行时需要的cost值。
 *  第二个数字4621.00表示执行整个SQL的cost

可以explain后加analyze来通过真实执行这个SQL来获得真实的执行计划和执行时间:.

osdba=\# EXPLAIN ANALYZE SELECT \* FROM t;

QUERY PLAN

\-----------------------------------------------------------------------------------------------------------

Seq Scan on t  (cost=0.00..4621.00 rows=300000 width=10) (actual time=0.022 ..355.380rows=300000 loops=1)

Total runtime: 696.074 ms

actual time=中的第一个数字表示返回第一行需要的时间(叫启动时间），第二个数字表示执行这个整个花的时间。后面的rows=300000是实际的行数。

表顺序扫描由于是立即可以获得第一行，所以启动时间一般都是0，而如果是排序操作，则需要处理完所有行后才能返回第一行，所以排序操作是需要启动时间的，下表列出了哪些操作是需要启动时间的，哪些操作不是需要的：

<table> 
 <tbody style="word-wrap:break-word"> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <th style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 执行计划运算类型</th> 
   <th style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 操作说明</th> 
   <th style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 是否有启动时间</th> 
  </tr> 
 </tbody> 
 <tbody style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Seq Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 扫描表</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Index Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 索引扫描</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Bitmap Index Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 索引扫描</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Bitmap Heap Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 索引扫描</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Subquery Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 子查询</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Tid Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> ctid = …条件</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Function Scan</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 函数扫描</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Nested Loop</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 循环结合</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Merge Join</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 合并结合</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Hash Join</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 哈希结合</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Sort</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 排序，ORDER BY操作</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Hash</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 哈希运算</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Result</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 函数扫描，和具体的表无关</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Unique</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> DISTINCT，UNION操作</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Limit</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> LIMIT，OFFSET操作</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Aggregate</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> count, sum,avg, stddev集约函数</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Group</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> GROUP BY分组操作</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Append</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> UNION操作</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 无启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> Materialize</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 子查询</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> 有启动时间</td> 
  </tr> 
  <tr style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:baseline; word-wrap:break-word"> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> SetOp</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> INTERCECT，EXCEPT</td> 
   <td style="margin:0px; padding:0px; border:0px currentColor; font-family:inherit; vertical-align:middle; word-wrap:break-word"> <p style="margin:0px; padding:0px; word-wrap:break-word">有启动时</p> </td> 
  </tr> 
 </tbody> 
</table>

explain select distinct course\_id from course where course\_term = 'Fal02';  
   
NOTICE:  QUERY PLAN:  
   
Unique  (cost=12223.09..12339.76 rows=4667 width=4)  
   
               -> Sort (cost=12223.09..12223.09 rows=46666 width=4)  
   
               ->  Seq Scan on course  (cost=0.00..8279.99 rows=46666 width=4)  
1.从下往上读  
2.explain报告查询的操作，开启的消耗，查询总的消耗，访问的行数 访问的平均宽度  
3.开启时间消耗是输出开始前的时间例如排序的时间  
4.消耗包括磁盘检索页，cpu时间   
5.注意，每一步的cost包括上一步的  
6.重要的是，explain 不是真正的执行一次查询 只是得到查询执行的计划和估计的花费  
   
索引有用条件 当满足特定条件的元组数小于总的数目

[http_blog.itpub.net_24484596_viewspace-730958]: http://blog.itpub.net/24484596/viewspace-730958/