postgresql执行计划理解

这里explain后加易做图yze来通过真实执行这个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 易做图yze和少部分DDL（如建立索引）后更新。
优化需要查看的系统表： 
 pg_stats 
 pg_statistic 
 pg_class 
 pg_stat是任何人都可以看的，而且可读性高，比较直观，pg_statistic只有superuser才能读，并且可读性差，普通人员建议看pg_stats，pg_stats是pg_statistic的视图。 这两个表也不是实时更新的，需要vacuum 易做图yze时会更新 
所涉及的系统变量： 
 default_statistics_target 
 geqo_threshold 
 join_collapse_limit 
 from_collapse_limit
可以通过 explain 加参数查看更详细的信息，
 ANALYZE ：执行命令并显示执行事件，默认false 
 VERBOSE ：对执行计划提供额外的信息，如查询字段信息等，默认false 
 COSTS ：显示执行计划的，默认true 
 BUFFERS ：默认false，前置条件是易做图yze 
 FORMAT ：默认格式是text 
explain (易做图yze,verbose,costs,buffers) select ...

将第一个in 语句改为左连接，小表查询，减少了连接的次数，性能有了明显提高：


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

可以看到，在查询2中，有nested loop left join 连接，但当我把 sql where 条件去掉后，sql 语句中的连接对应在查询计划中都变成了 hash连接，而且性能差很多，因此尽量在有join的多表查询中 包含where 条件， 查询3：
1 explain 易做图yze 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 易做图yze 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。