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 analyzeselectr.*,a.usernamefromt_portal_resource r
2 leftjoint_uc_account aonr.userid=a.idwherer.idin(resourceid
3 t_portal_cate_rescategoryid(1))
4 and( r.titlelike'%低调%'orr.tags@H_332_404@'%net%')orderbyistopdesc,r.idlimit 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值。第二个数字表示执行整个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

@H_404_633@r.userid=a.id(1)) tr.id=t.resourceidwhere
(r.titlelimit 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:

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

查询4

rs.resourceidt_portal_cate_res rs
t_portal_cate_user cars.categoryid=ca.categoryid
但是当把查询4 加上where条件后 where ca.userid=1. 就会变成nest loop 查询

所以在many-to-many 的表连接查询的时候尽量转换为小表 one-to-many 查询加上where 条件等等方式优化sql

猜你在找的Postgre SQL相关文章