先看一份执行计划

1
2
3
4
5
6
7

skytf=> explain (analyze on ,buffers on ) select * from test_15;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------  
Seq Scan on test_15 (cost=0.00..155.00 rows=10000 width=11) (actual time=0.009..6.160 rows=10000 loops=1)  
 Buffers: shared hit=55  
Total runtime: 11.662 ms  
(3 rows)

这是一个全表扫描的PLAN，cost=0.00..155.00, 然而，这个 cost=155 是如何计算得呢？这篇日志将针对全表扫描的前提下，讲解下 PLAN中 cost 的计算方法，在此之前，先看下与PLAN相关的系统参数。

执行计划相关参数

seq_page_cost (floating point)

Sets the planner is estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0.
全表扫描时读取每个页面的代价值，默认为 1.0

cpu_tuple_cost (floating point)

Sets the planner is estimate of the cost of processing each row during a query. The default is 0.01.
处理每行记录花费的代价，默认为 0.01

cpu_index_tuple_cost (floating point)

Sets the planner is estimate of the cost of processing each index entry during an index scan. The default is 0.005.
每次索引查询进入索引处理的代价，默认为 0.005

cpu_operator_cost (floating point)

Sets the planner is estimate of the cost of processing each operator or function executed during a query. The default is 0.0025.

执行计划 Cost 值验证

删除原表 test_15

1

skytf=> drop table test_15

创建测试表 并插入数据

1
2
3
4

skytf=> create table test_15(id integer,name varchar(32));  
CREATE TABLE
skytf=> insert into test_15 select generate_series(1,10000),'francs';  
INSERT 0 10000

表分析

1
2
3
4
5
6
7

skytf=> analyze test_15;  
ANALYZE
skytf=> select pg_size_pretty(pg_relation_size('test_15'));  
pg_size_pretty  
----------------  
440 kB  
(1 row)

查看下PLAN

1
2
3
4
5
6
7

skytf=> explain (analyze on ,buffers on ) select * from test_15;  
 QUERY PLAN  
------------------------------------------------------------------------------------------------------------  
Seq Scan on test_15 (cost=0.00..155.00 rows=10000 width=11) (actual time=0.009..6.160 rows=10000 loops=1)  
 Buffers: shared hit=55  
Total runtime: 11.662 ms  
(3 rows)

查询seq_page_cost，和 cpu_tuple_cost

1
2
3
4
5
6
7
8
9
10
11
12
13
14

skytf=> SELECT  
skytf-> relpages,  
skytf-> current_setting('seq_page_cost') AS seq_page_cost,  
skytf-> relpages *  
skytf-> current_setting('seq_page_cost')::decimal AS page_cost,  
skytf-> reltuples,  
skytf-> current_setting('cpu_tuple_cost') AS cpu_tuple_cost,  
skytf-> reltuples *  
skytf-> current_setting('cpu_tuple_cost')::decimal AS tuple_cost  
skytf-> FROM pg_class WHERE relname='test_15';  
relpages | seq_page_cost | page_cost | reltuples | cpu_tuple_cost | tuple_cost  
----------+---------------+-----------+-----------+----------------+------------  
 55 | 1 | 55 | 10000 | 0.01 | 100  
(1 row)

从上面可以看出，表 “test_15” 占用 55个 page, 共有 10000条记录，全表扫描的过程可以理解成 PG会扫描这张表上所有的页,并且处理页上的每条记录，所以COST可以根据以下公式来计算
全表扫描 Cost= relpages seq_page_cost参数值 + reltuples cpu_tuple_cost参数值

所以上述 Plan的 cost =551 + 100000.01=155

附：SQL查询

查询relpages,reltuples,和当前 seq_page_cost,cpu_tuple_cost 参数值。

1
2
3
4
5
6
7
8

SELECT  
 relpages,  
 current_setting('seq_page_cost') AS seq_page_cost,  
 relpages * current_setting('seq_page_cost')::decimal AS page_cost,  
 reltuples,  
 current_setting('cpu_tuple_cost') AS cpu_tuple_cost,  
 reltuples * current_setting('cpu_tuple_cost')::decimal AS tuple_cost  
FROM pg_class WHERE relname='test_15';

总结

以上实验只是针对 “Seqences Scan” 的方式计算 Cost, 其它扫描方式如索引扫描，表的其它连接方式的COST计算方法又不相同，这里不再实验了。