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。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值