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)