postgresql 执行计划理解
首先看下postgresql 执行计划中的一些术语和关键字。
例子,查询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),其中影响成本计算的参数包括(后面括号的值为其缺省值):
从第一行起,主要查看 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(如建立索引)后更新。 优化需要查看的系统表: 可以通过 explain 加参数查看更详细的信息, ANALYZE :执行命令并显示执行事件,默认false 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 对应在查询计划里有下面几种
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。 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |