PostgreSQL执行计划和成本因子详解

PG对各表的统计信息:pg_stats

资源来源:PostgreSQL 9.3.1 中文文档 —— Chapter 47. 系统表 —— 47.68. pg_stats

名字 类型 引用 描述
schemaname name pg_namespace.nspname 包含此表的模式名字
tablename name pg_class.relname 表的名字
attname name pg_attribute.attname 这一行描述的字段的名字
inherited bool 如果为真,那么这行包含继承的子字段,不只是指定表的值。
null_frac real 记录中字段为空的百分比
avg_width integer 字段记录以字节记的平均宽度
n_distinct real 如果大于零,就是在字段中独立数值的估计数目。如果小于零, 就是独立数值的数目被行数除的负数。用负数形式是因为ANALYZE 认为独立数值的数目是随着表增长而增长; 正数的形式用于在字段看上去好像有固定的可能值数目的情况下。比如, -1 表示一个唯一字段,独立数值的个数和行数相同。
most_common_vals anyarray 一个字段里最常用数值的列表。如果看上去没有啥数值比其它更常见,则为 null
most_common_freqs real[] 一个最常用数值的频率的列表,也就是说,每个出现的次数除以行数。 如果most_common_vals是 null ,则为 null。
histogram_bounds anyarray 一个数值的列表,它把字段的数值分成几组大致相同热门的组。 如果在most_common_vals里有数值,则在这个饼图的计算中省略。 如果字段数据类型没有<操作符或者most_common_vals 列表代表了整个分布性,则这个字段为 null。
correlation real 统计与字段值的物理行序和逻辑行序有关。它的范围从 -1 到 +1 。 在数值接近 -1 或者 +1 的时候,在字段上的索引扫描将被认为比它接近零的时候开销更少, 因为减少了对磁盘的随机访问。如果字段数据类型没有<操作符,那么这个字段为null。
most_common_elems anyarray 经常在字段值中出现的非空元素值的列表。(标量类型为空。)
most_common_elem_freqs real[] 最常见元素值的频率列表,也就是,至少包含一个给定值的实例的行的分数。 每个元素频率跟着两到三个附加的值;它们是在每个元素频率之前的最小和最大值, 还有可选择的null元素的频率。(当most_common_elems 为null时,为null)
elem_count_histogram real[] 该字段中值的不同非空元素值的统计直方图,跟着不同非空元素的平均值。(标量类型为空。)

成本因子

因为PostgreSQL是基于代价模型来选择最优的执行计划的,而成本因子则是计算代价模型的最重要参数。(代价=CPU代价+IO代价+数据传输[如网络]代价)

在PG9.4默认情况下的成本因子如下:(这些值可以在 postgresql.conf 文件里修改的)

1
2
3
4
5
6
7
8
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale。扫描一个数据块(一页)的成本(IO成本)
#random_page_cost = 4.0 # same scale as above。随机获取一个数据块(一页)的成本(IO成本)
#cpu_tuple_cost = 0.01 # same scale as above。获取一行数据的CPU成本
#cpu_index_tuple_cost = 0.005 # same scale as above。获取一个索引项的CPU成本
#cpu_operator_cost = 0.0025 # same scale as above。每个操作符的CPU成本
#effective_cache_size = 4GB #评估操作系统缓存可能使用的内存大小。用于评估索引扫描的开销,大的值倾向使用索引,小的值倾向使用全表扫描。一般设置为“物理内存 - shared buffers - 内核和其他软件占用的内存”。

注意:SSD的随机读和顺序读差别不是太大,这时可以缩小 seq_page_costrandom_page_cost 之间的大小。使random_page_cost趋向于seq_page_cost

关于 effective_cache_size 特别说明一下

资料来源

effective_cache_size用于在Linux操作系统上报告内核缓存的大小,我想强调一下它在postgresql.conf配置里的重要性。
effective_cache_size

不像其他内存那样是设置已经分配好的控制内存,effective_cache_size用于告诉优化器在内核里有多少cache(读缓存)。这对于决定代价高的索引扫描方式是非常重要的。优化器知道 shared_buffers 大小,但是不知道内核缓存大小,从而影响到代价非常高的磁盘访问。

内核缓存大小改变比较频繁,所以,正常地运行一段时间的系统负载,然后使用该内存值去设置 effective_cache_size。这个值不必是非常完美的,仅仅只是粗略地估计还有多少内核内存,相当于是shared buffers的二级缓存。

explain 输出及详解

explain 语法:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
test=# \h explain;
Command: EXPLAIN
Description: show the execution plan of a statement
Syntax:
EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement
where option can be one of:
ANALYZE [ boolean ] 是否真正执行
VERBOSE [ boolean ] 显示详细信息
COSTS [ boolean ] 显示代价信息
BUFFERS [ boolean ] 显示缓存信息
TIMING [ boolean ] 显示时间信息
FORMAT { TEXT | XML | JSON | YAML } 输出格式,默认为 text

图解:

注意

看执行计划,是从最底层开始往后看的。即先从 节点1,再到节点2,最后到节点3。
而且,上一级节点的成本,是包含了下一级的成本的。比如:节点2的启动成本和结束成本是已经包含了节点1的启动成本和结束成本的,由此可以得出一个结论:就是上一级节点的启动成本和结束成本永远不会比下一级的小。

再次强调一下,每个节点的估算的总启动或结束成本只是平均每次loops的平均成本,所以最后的总成本还要乘以loops次数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
test=# explain(analyze, timing, verbose, buffers,costs) select max(sum) from ( select count(*) as sum from tgroup group by point) as t;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=235363.15..235363.16 rows=1 width=8) (actual time=4898.900..4898.900 rows=1 loops=1)
Output: max((count(*)))
Buffers: shared hit=12770 read=49578
-> HashAggregate (cost=235363.04..235363.09 rows=5 width=4) (actual time=4898.890..4898.891 rows=5 loops=1)
Output: count(*), tgroup.point
Group Key: tgroup.point
Buffers: shared hit=12770 read=49578
-> Seq Scan on public.tgroup (cost=0.00..177691.36 rows=11534336 width=4) (actual time=0.045..1643.984 rows=11534336 loops=1)
Output: tgroup.id, tgroup.age, tgroup.point
Buffers: shared hit=12770 read=49578
Planning time: 0.170 ms
Execution time: 4898.982 ms
(12 rows)
Time: 4899.758 ms
test=#

估算成本的计算公式

全表扫描时计算:

公式:total cost = relpages * seq_page_cost + reltuples * cpu_tuple_cost。

1
2
3
4
5
6
test=# select relpages, reltuples from pg_class where relname = 'tgroup';
-[ RECORD 1 ]----------
relpages | 62348
reltuples | 1.15343e+07
Time: 0.751 ms
1
2
3
4
5
test=# show seq_page_cost ;
-[ RECORD 1 ]-+--
seq_page_cost | 1
Time: 28.848 ms
1
2
3
4
5
test=# show cpu_tuple_cost;
-[ RECORD 1 ]--+-----
cpu_tuple_cost | 0.01
Time: 0.460 ms

如上节点1执行计划,是全表扫描:
177691.36 = 62348 * 1 + 1.15343e+07 * 0.01

1
2
3
4
5
6
test=# select 62348 * 1 + 1.15343e+07 * 0.01;
-[ RECORD 1 ]-------
?column? | 177691.00
Time: 39.815 ms
test=#

这个与结果相符。

全表顺序扫描并过滤,代价公式为:

Cost = seq_scan_cost*relpages + cpu_tuple_cost*reltuples + cpu_operator_cost*reltuples

扫描的方式

1
2
3
4
5
6
7
8
9
10
11
enable_bitmapscan = on
enable_hashagg = on
enable_hashjoin = on
enable_indexscan = on #索引扫描
enable_indexonlyscan = on #只读索引扫描
enable_material = on #物化视图
enable_mergejoin = on
enable_nestloop = on
enable_seqscan = on
enable_sort = on
enable_tidscan = on

虽然我们不能强制指定PostgreSQL按我们写的SQL来执行(无视优化器),但是可以通过改变某些的查询方式代价从而影响PostgreSQL的查询优化器的选择。
这时,我们可以从上面的扫描方式中将其修改为 off(其实不是强制不能以某种方式扫描,将其设置为 off时,只是将该项的扫描代价提高到非常大的值,从而让PostgreSQL尽可能避免使用该方式来进行扫描,但不是绝对的,如果其他的方式比off的代价更大,那PostgreSQL还是会选择代价最小的来执行的),这就为我们提供了非常好的控制我们SQL的扫描方式。