PostgreSQL DBA(175) - Cost EST（SeqScan）

本节简单介绍了顺序扫描的成本估算。

成本估算

数据库基于查询成本估算进行查询优化，具体操作的成本使用相对成本，如默认的数据库顺序扫描一个数据库块的成本为1，随机读写一个块的成本为4。执行器负责执行的所有操作均有相应的成本函数，如顺序扫描的成本函数为cost_seqscan，索引扫描的成本函数为cost_index。
在数据库中，有三种类型的成本，分别是启动成本、运行成本和总成本，其中总成本=启动成本+运行成本。
1) 启动成本：是指第一个元组返回前的成本。比如顺序扫描的成本是读取数据库访问目标表的第一个元组的成本。
2) 运行成本：提取所有元组的成本。
3) 总成本：上述两种类型的成本之和。
下面以顺序扫描、索引扫描和排序操作为例详细解释成本估算，其中测试脚本如下：
DROP TABLE t_tbl;
CREATE TABLE t_tbl (id int PRIMARY KEY, value int);
CREATE INDEX idx_t_tbl_value ON t_tbl (value);
INSERT INTO t_tbl SELECT x,x from generate_series(1,10000) x;
ANALYZE t_tbl;
数据表信息
atlasdb=# \d+ t_tbl;
Table “public.t_tbl”
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
————+————-+—————-+—————+————-+————-+———————+——————-
id | integer | | not null | | plain | |
value | integer | | | | plain | |
Indexes:
“t_tbl_pkey” PRIMARY KEY, btree (id)
“idx_t_tbl_value” btree (value)

顺序扫描

顺序扫描通过函数cost_seqscan进行估算，下面通过以下查询语句来解释估算过程。
SELECT FROM t_tbl WHERE id < 5000;
顺序扫描的启动成本为0，运行成本估算公式为：
运行成本 = CPU运行成本 + 磁盘运行成本
=（CPU元组处理成本 + CPU操作成本） 元组个数 + 顺序扫描数据块成本 块数
=(cpu_tuple_cost + cpu_operator_cost)TuplesOfTable+seq_page_cost PagesOfTable
其中处理每个元组的CPU成本、CPU操作成本（比如大小比较、条件过滤等）和顺序扫描数据块成本通过配置参数定义，默认分别是0.01、0.0025和1。元组个数和块数通过查询数据字典可得，分别是10000和45：
atlasdb=# SELECT relpages, reltuples FROM pg_class WHERE relname = ‘t_tbl’;
relpages | reltuples
—————+—————-
45 | 10000
(1 row)
根据成本计算公式，顺序扫描的成本为：
运行成本 = （0.01 + 0.0025）10000 + 1 45 = 170
查看该SQL语句的执行计划，数据库优化器估算的实际运行成本是170，与计算公式结果相符：
atlasdb=# explain SELECT FROM t_tbl WHERE id < 5000;
QUERY PLAN
-————————————————————————————-
Seq Scan on t_tbl (cost=0.00..170.00 rows=4999 width=8)
Filter: (id < 5000)
(2 rows)
可以看到，虽然带有条件id < 5000，但优化器仍选择全表顺序扫描。
值得注意的是，如没有id < 5000这个条件，那么运行成本为：
=CPU元组处理成本 元组个数 + 顺序扫描数据块成本块数
atlasdb=# explain SELECT * FROM t_tbl;
QUERY PLAN
-—————————————————————————————
Seq Scan on t_tbl (cost=0.00..145.00 rows=10000 width=8)
(1 row)