当前位置:数据库 > SQLServer >>

postgresql执行计划理解

postgresql 执行计划理解
 
首先看下postgresql 执行计划中的一些术语和关键字。
执行计划运算类型
操作说明
是否有启动时间
Seq Scan
扫描表
无启动时间
Index Scan
索引扫描
无启动时间
Bitmap Index Scan
索引扫描
有启动时间
Bitmap Heap Scan
索引扫描
有启动时间
Subquery Scan
子查询
无启动时间
Tid Scan
ctid = …条件
无启动时间
Function Scan
函数扫描
无启动时间
Nested Loop
循环结合
无启动时间
Merge Join
合并结合
有启动时间
Hash Join
哈希结合
有启动时间
Sort
排序,ORDER BY操作
有启动时间
Hash
哈希运算
有启动时间
Result
函数扫描,和具体的表无关
无启动时间
Unique
DISTINCT,UNION操作
有启动时间
Limit
LIMIT,OFFSET操作
有启动时间
Aggregate
count, sum,avg, stddev集约函数
有启动时间
Group
GROUP BY分组操作
有启动时间
Append
UNION操作
无启动时间
Materialize
子查询
有启动时间
SetOp
INTERCECT,EXCEPT
有启动时
例子,查询1:
1
explain analyze select r.*,a.username  from t_portal_resource r
2
left join t_uc_account a on r.userid=a.id where  r.id in (select resourceid
3
from t_portal_cate_res where categoryid in (1))
4
and ( r.title like '%低调%' or r.tags like '%net%' )  order by istop desc, r.id desc limit 10 offset 0

这里explain后加analyze来通过真实执行这个SQL来获得真实的执行计划和执行时间。

postgresql查询计划是按照成本计算的,也就是基于成本的查询计划(cost-based plan),其中影响成本计算的参数包括(后面括号的值为其缺省值):
cpu_index_tuple_cost (0.005)
cpu_operator_cost (0.0025)
cpu_tuple_cost (0.01)
random_page_cost (4.0)
seq_page_cost (1.0)
 从第一行起,主要查看 cost。 例如上面 cost=11.61..11.61。cost=说明:第一个数字11.61表示启动cost,这是执行到返回第一行时需要的cost值。第二个数字11.61表示执行整个SQL的cost
 actual time=0.183..0.191 rows=3 loops=1。actual time=中的第一个数字表示返回第一行需要的时间(叫启动时间),第二个数字表示执行这个整个花的时间。后面的rows=3是实际的行数。
一个查询的总代价包括读取数据的I/O代价和其他各种操作的代价之和。 I/O代价包括顺序读取数据或索引页(seq_scan_cost)和随机读取数据页(random_scan_cost)的代价,操作代价包括处理表元组(cpu_tuple_cost)、处理比较操作(cpu_operator_cost)和处理索引元组(cpu_index_tuple_cost)。
比如,如果在一个表上做全表顺序扫描,那么其代价公式为:
 Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples
如果是在一个表上做全表顺序扫描并执行过滤,则代价公式为:
 Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples + cpu_operator_cost*reltuples
对于预算要返回的行数量,其计算公式为:
 rows = reltuples*估算频率
这里,估算频率通过sys_stats视图中统计的列值和出现频率计算得出
relpages磁盘页,reltuples是行数(与实际不一定相符,一般略小)
select relpages,reltuples from pg_class where relname = 't_portal_resource'; 可以查看对象的详细信息。pg_class中的relpages,reltuples数据不是实时更新的,一般在vacuum analyze和少部分DDL(如建立索引)后更新。
优化需要查看的系统表: 
 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
可以通过 explain 加参数查看更详细的信息,
 ANALYZE :执行命令并显示执行事件,默认false 
 VERBOSE :对执行计划提供额外的信息,如查询字段信息等,默认false 
 COSTS :显示执行计划的,默认true 
 BUFFERS :默认false,前置条件是analyze 
 FORMAT :默认格式是text 
explain (analyze,verbose,costs,buffers) select ...
我将上面的sql 修改了一下。查询2:
1
explain analyze select r.*,a.username  from t_portal_resource r
2
left join t_uc_account a on r.userid=a.id left join (select resourceid
3
from t_portal_cate_res where categoryid in (1)) t on r.id=t.resourceid where
4
(r.title like '%低调%' or r.tags like '%net%') order by istop desc, r.id desc limit 10 offset 0
将第一个in 语句改为左连接,小表查询,减少了连接的次数,性能有了明显提高:


一般来说sql 中的连接 join 对应在查询计划里有下面几种
嵌套循环连接(Nested loop join)
带内表顺序扫描
带内表索引扫描
合并连接(Merge join)
哈希连接(Hash join)

可以看到,在查询2中,有nested loop left join 连接,但当我把 sql where 条件去掉后,sql 语句中的连接对应在查询计划中都变成了 hash连接,而且性能差很多,因此尽量在有join的多表查询中 包含where 条件, 查询3:
1 explain analyze select r.*,a.username from t_portal_resource r
2 left join t_uc_account a on r.userid=a.id left join (select resourceid
3 from t_portal_cate_res where categoryid in (1)) t on r.id=t.resourceid order by istop desc, r.id desc limit 10 offset 0


那么什么时候会参数合并merge join 呢。many-to-many 的查询,例如t_portal_cate_res(resourceid,categoryid) 和 t_portal_cate_user(categoryid,userid)
查询4:

1 explain analyze select rs.resourceid from t_portal_cate_res rs
2 left join t_portal_cate_user ca on rs.categoryid=ca.categoryid


但是当把查询4 加上where条件后 where ca.userid=1. 就会变成nest loop 查询。
所以在many-to-many 的表连接查询的时候尽量转换为小表 one-to-many 查询,加上where 条件等等方式优化sql。

Oracle
MySQL
Access
SQLServer
DB2
Excel
SQLite
SYBASE
Postgres
如果你遇到数据库难题:
请访问www.zzzyk.com 试试
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,