本节介绍代价计算和相关计算公式。

代价计算模型

操作成本 默认值 说明
seq_page_cost 1.0 顺序扫描一个页面的成本。
random_page_cost 4.0 随机扫描一个页面的成本。
cpu_tuple_cost 0.01 CPU处理一行的成本。
cpu_index_tuple_cost 0.005 CPU处理一个索引项的成本。
cpu_operator_cost 0.0025 CPU处理每个函数或操作符的成本。

全表扫描

postgres=# explain select * from test where id <1000;
                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on test  (cost=0.00..1693.00 rows=5680 width=4)
   Filter: (id < 1000)
(2 rows)

postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages | reltuples
 ------------+-----------
        443  |    100000
(1 row)

计算公式如下：

  • TOTAL_COST = CPU代价 + IO代价
  • CPU代价 = cpu_tuple_cost * reltuples + cpu_operator_cost * reltuples
  • CPU代价 = 0.01 * 100000 + 0.0025 * 100000 = 1250.0
  • IO代价 = seq_page_cost * relpages
  • IO代价 = 1.0 * 443 = 443
  • TOTAL_COST = 1250 + 443 = 1693

索引扫描

postgres=# explain select * from test where id =1414;
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.29..72.66 rows=30 width=4)
   Index Cond: (id = 1414)
(2 rows)

计算公式如下：

  • RUN_COST = CPU代价 + IO代价
  • CPU代价 = 索引 CPU代价 + 表 CPU代价
  • 索引CPU代价 = 选择率 * 索引行数 * (cpu_index_tuple_cost + cpu_operator_cost )
  • 表 CPU代价 = 选择率 * 数据行数 * cpu_tuple_cost

索引CPU代价计算

postgres=# select most_common_freqs[array_position((most_common_vals::text)::real[],1414::real)] from pg_stats where tablename ='test';
 most_common_freqs
-------------------
            0.0003

## 选择率 = 0.0003

postgres=# select relpages,reltuples from pg_class where relname='test_id_idx';
 relpages | reltuples
    ----------+-----------
          276 |    100000

## 索引行数 = 100000 ，索引页面数 = 276

postgres=# select relpages,reltuples from pg_class where relname='test';
 relpages | reltuples
    ----------+-----------
          443 |    100000

## 表行数 = 100000 ，表页面数 = 443

通过以上计算示例得出CPU代价如下：

  • 索引CPU代价 = 0.0003 * 100000 * (0.0025 + 0.005) = 0.22500000
  • 表CPU代价 = 0.0003 * 100000 * 0.01 = 0.300000
  • CPU代价 = 0.300000 + 0.22500000 = 0.52500000

索引IO代价计算

索引IO代价 = ceil (选择率 * 索引页面数) * random_page_cost

表IO代价 = max_io_cost + 相关系数 * 相关系数 * (min_io_cost – max_io_cost)

max_io_cost = 选择率 * 数据行数 * random_page_cost = 120

min_io_cost = 1 * random_page_cost + (ceil(选择率 * 表页面数) - 1) * seq_page_cost = 4

postgres=# select correlation from pg_stats where tablename ='test';
 correlation
-------------
    0.670535

相关系数 = 0.670535

IO代价 = 4 + 67.844406397900 = 71.844406397900

通过以上计算示例得出IO代价如下：

  • RUN_COST = 71.844406397900 + 0.52500000 = 72.369406397900
  • START_COST = 0.29
  • TOTAL_COST = 72.369406397900 + 0.29 = 72.659406397900 ≈ 72.66