postgresql 执行计划理解

2019独角兽企业重金招聘Python工程师标准>>>

首先看下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：

explain analyze select r.*,a.username  from t_portal_resource r 
left join t_uc_account a on r.userid=a.id where  r.id in (select resourceid 
from t_portal_cate_res where categoryid in (1)) 
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：

explain analyze select r.*,a.username  from t_portal_resource r 
left join t_uc_account a on r.userid=a.id left join (select resourceid 
from t_portal_cate_res where categoryid in (1)) t on r.id=t.resourceid where 
(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：

explain analyze select r.*,a.username  from t_portal_resource r 
left join t_uc_account a on r.userid=a.id left join (select resourceid 
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：

explain analyze select rs.resourceid from t_portal_cate_res rs 
 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。

转载于:https://my.oschina.net/congqian/blog/135872