postgresql 执行计划理解
|
首先看下postgresql 执行计划中的一些术语和关键字。
例子，查询1：
4 |
and( r.titlelike'%低调%'orr.tags'%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：
(1)) tr.id=t.resourceidwhere
limit 10 offset 0