知識星球（點選上方公眾號，可快速關註）
英文：igor_sarcevic，翻譯：CSDN/無阻我飛揚
blog.csdn.net/dev_csdn/article/details/78841624
摘要：PostgreSQL是一個自由的物件-關係資料庫伺服器(資料庫管理系統)，被業界譽為“最先進的開源資料庫”，本文作者以程式碼實體說明瞭如何計算和評估PostgreSQL查詢成本，以下是譯文。
資料庫查詢速度如果太慢會從很多方面損害機構，比如可能會損害一些優秀應用程式的聲譽，因為資料庫查詢速度過慢，造成後臺處理速度慢得令人痛苦，並大幅增加基礎設施的成本。作為一名經驗豐富的Web開發人員，瞭解資料層的最佳化策略是絕對必要的。
在本文中，我們將探討PostgreSQL的成本模型，如何瞭解explain命令（關於explain命令可參見這篇文章）的輸出，最重要的是如何利用這些模型資料來提高應用程式的吞吐量。
使用PostgreSQL Explain 命令
在應用程式中部署一個新的查詢陳述句之前，最好透過PostgreSQL中的 explain中的命令來執行它，以評估新查詢對應用系統效能的影響。
以一個示例資料庫表開始，來說明explain命令的用法。這個表存有一百萬條資料記錄。
db # CREATE TABLE users (id serial, name varchar);
db # INSERT INTO users (name) SELECT ‘John’
FROM generate_series(1, 1000000);
db # SELECT count(*) FROM users;
count
1000000
(1 row)
db # SELECT id, name FROM users LIMIT 10;
id | name
—-+——
1 | John
2 | John
3 | John
4 | John
5 | John
6 | John
7 | John
8 | John
9 | John
10 | John
(10 rows)
假設需要用一個給定的id來查詢一個使用者名稱，但是在部署新的查詢程式碼之前，要評估這個查詢操作的成本。執行一個explain陳述句來做相關查詢：
db # EXPLAIN SELECT * FROM users WHERE id = 870123;
QUERY PLAN
————————————————–
Gather (cost=1000.00..11614.43 rows=1 width=9)
Workers Planned: 2
-> Parallel Seq Scan on users (cost=0.00..10614.33 rows=1 width=9)
Filter: (id = 870123)
(4 rows)
在上面的例子中有很多的輸出，但是可以得到它的要點。為了執行這個查詢，PostgreSQL計劃啟動兩個並行的工作行程（workers）。每個工作行程將在表上進行順序掃描，最後，收集器合併來自兩個工作行程的結果。
在本文中，重點介紹上面輸出的cost以及PostgreSQL如何計算它。
為了簡化成本探索，執行上面的查詢，但限制可並行的工作行程數量為0。
db # SET max_parallel_workers_per_gather = 0;
db # EXPLAIN SELECT * FROM users WHERE id = 870123;
QUERY PLAN
————————————————–
Seq Scan on users (cost=0.00..17906.00 rows=1 width=9)
Filter: (id = 870123)
(2 rows)
這有點簡單。在只有單CPU內核的情況下，評估成本是17906。
成本值背後的數學
在PostgreSQL中，成本或懲罰點大多是一個抽象的概念。PostgreSQL可以執行查詢的方式很多，而PostgreSQL總是選擇最低成本值的執行規劃。
計算成本，PostgreSQL首先查看錶的位元組數大小。接下來看看使用者表的大小。
db # select pg_relation_size(‘users’);
pg_relation_size
————————–
44285952
(1 row)
PostgreSQL會為每個要依次讀取的塊新增成本點。如果知道每個塊都包含了8kb，那麼就可以計算從表中讀取的順序塊的成本值。
block_size = 8192 # block size in bytes
relation_size = 44285952
blocks = relation_size / block_size # => 5406
現在，已經知道塊的數量，找出PostgreSQL為每個塊讀取分配多少個成本點。
db # SHOW seq_page_cost;
seq_page_cost
———-
1
(1 row)
換句話說，PostgreSQL為每個塊分配一個成本點。這就需要 5406個成本點從表中讀取資料。
從磁碟讀取值並不是PostgreSQL需要做的。它必須將這些值傳送給CPU並應用一個WHERE子句過濾。對於這個計算來說，如下的兩個值非常有趣。
db # SHOW cpu_tuple_cost;
cpu_tuple_cost
——————–
0.01
db # SHOW cpu_operator_cost;
cpu_operator_cost
—————-
0.0025
現在，用所有的值來計算在explain 陳述句中得到的值。
number_of_records = 1000000
block_size = 8192 # block size in bytes
relation_size = 44285952
blocks = relation_size / block_size # => 5406
seq_page_cost = 1
cpu_tuple_cost = 0.01
cpu_filter_cost = 0.0025;
cost = blocks * seq_page_cost +
number_of_records * cpu_tuple_cost +
number_of_records * cpu_filter_cost
cost # => 17546
索引和PostgreSQL成本模型
索引在資料庫工程師的生活中很可能仍然是最重要的話題。新增索引是否可以降低SELECT陳述句的成本呢？透過下麵的例子來找出答案。
首先，在users表中新增一個索引：
db # CREATE INDEX idx_users_id ON users (id);
觀察一下新索引的查詢規劃。
db # EXPLAIN SELECT * FROM users WHERE id = 870123;
QUERY PLAN
———- ———- ———-
Index Scan using idx_users_id on users (cost=0.42..8.44 rows=1 width=9)
Index Cond: (id = 870123)
(2 rows)
成本函式顯著下降。索引掃描的計算比順序掃描的計算要複雜一些。它由兩個階段組成。
PostgreSQL會考慮random_page_cost和cpu_index_tuple_cost 變數，並傳回一個基於索引樹的高度的值。
db # SHOW random_page_cost;
random_page_cost
—————-
4
db # SHOW cpu_index_tuple_cost;
cpu_index_tuple_cost
———-
0.005
對於實際的計算，請考慮閱讀成本指數計算器的原始碼。
工作行程的成本
PostgreSQL可以啟動並行的工作行程（worker）來執行查詢。但是，開啟一個新的工作行程，效能會受到影響。
為了計算使用並行工作行程的成本，PostgreSQL使用 parallel_tuple_cost這個命令，它定義了從一個工作行程傳送元組到另一個工作行程的成本，parallel_setup_cost命令意味著啟動一個新的工作行程（worker）的成本，以下是查詢示例。
db # SHOW parallel_tuple_cost;
parallel_tuple_cost
———————
0.1
db # SHOW parallel_setup_cost;
parallel_setup_cost
———————
1000
看完本文有收穫？請轉發分享給更多人
關註「資料庫開發」，提升 DB 技能
淘口令：複製以下紅色內容，再開啟手淘即可購買
範品社，使用￥極客T恤￥搶先預覽（長按複製整段文案，開啟手機淘寶即可進入活動內容）
近期，北京地區正常發貨，但派件時間有所延長。
