执行计划评估

假设test表有10000条数据，通过下面命令查询分布在94个pages上。

tt=# select relpages,reltuples from pg_class where relname = 'test';
relpages | reltuples
----------+-----------
94 | 10000
(1 row)

Time: 0.576 ms
tt=# select name,setting from pg_settings where name like '%cost%' and name not like '%vacuum%';
name | setting
-------------------------+---------
cpu_index_tuple_cost | 0.005
cpu_operator_cost | 0.0025
cpu_tuple_cost | 0.01
jit_above_cost | 100000
jit_inline_above_cost | 500000
jit_optimize_above_cost | 500000
parallel_setup_cost | 1000
parallel_tuple_cost | 0.1
random_page_cost | 4
seq_page_cost | 1
(10 rows)

tt=# explain select * from test;
QUERY PLAN
-----------------------------------------------------------
Seq Scan on test (cost=0.00..194.00 rows=10000 width=45)
(1 row)

Time: 0.392 ms

cost描述一个SQL执行的代价是多少，而不是具体的时间。

评估时间 =（磁盘页*seq_page_cost）+（扫描行*cpu_tuple_cost）。

 

默认seq_page_cost是1.0，cpu_tuple_cost是0.01，所以评估值是(94 * 1.0) + (10000 * 0.01) = 194。