摘要：PostgreSQL是一個自由的物件-關聯式資料庫伺服器(資料庫管理系統)，被業界譽為“最先進的開源資料庫”，本文作者以程式碼例項說明了如何計算和評估PostgreSQL查詢成本，以下是譯文。
資料庫查詢速度如果太慢會從很多方面損害機構，比如可能會損害一些優秀應用程式的聲譽，因為資料庫查詢速度過慢，造成後臺處理速度慢得令人痛苦，並大幅增加基礎設施的成本。作為一名經驗豐富的Web開發人員，瞭解資料層的優化策略是絕對必要的。
在本文中，我們將探討PostgreSQL的成本模型，如何瞭解explain命令（關於explain命令可參見這篇文章）的輸出，最重要的是如何利用這些模型資料來提高應用程式的吞吐量。
使用PostgreSQL Explain 命令
在應用程式中部署一個新的查詢語句之前，最好通過PostgreSQL中的 explain中的命令來執行它，以評估新查詢對應用系統效能的影響。
以一個示例資料庫表開始，來說明explain命令的用法。這個表存有一百萬條資料記錄。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
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語句來做相關查詢：
|
1 2 3 4 5 6 7 8 9 10 11 |
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。
|
1 2 3 4 5 6 7 8 9 10 11 |
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首先檢視錶的位元組數大小。接下來看看使用者表的大小。
|
1 2 3 4 5 6 7 |
db # select pg_relation_size('users'); pg_relation_size -------------------------- 44285952 (1 row) |
PostgreSQL會為每個要依次讀取的塊新增成本點。如果知道每個塊都包含了8kb，那麼就可以計算從表中讀取的順序塊的成本值。
|
1 2 3 4 5 |
block_size = 8192 # block size in bytes relation_size = 44285952 blocks = relation_size / block_size # => 5406 |
現在，已經知道塊的數量，找出PostgreSQL為每個塊讀取分配多少個成本點。
|
1 2 3 4 5 6 |
db # SHOW seq_page_cost; seq_page_cost ---------- 1 (1 row) |
換句話說，PostgreSQL為每個塊分配一個成本點。這就需要 5406個成本點從表中讀取資料。
從磁碟讀取值並不是PostgreSQL需要做的。它必須將這些值傳送給CPU並應用一個WHERE子句過濾。對於這個計算來說，如下的兩個值非常有趣。
|
1 2 3 4 5 6 7 8 9 10 |
db # SHOW cpu_tuple_cost; cpu_tuple_cost -------------------- 0.01 db # SHOW cpu_operator_cost; cpu_operator_cost ---------------- 0.0025 |
現在，用所有的值來計算在explain 語句中得到的值。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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表中新增一個索引：
|
1 2 |
db # CREATE INDEX idx_users_id ON users (id); |
觀察一下新索引的查詢規劃。
|
1 2 3 4 5 6 7 8 |
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 變數，並返回一個基於索引樹的高度的值。
|
1 2 3 4 5 6 7 8 9 10 11 |
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）的成本，以下是查詢示例。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
db # SHOW parallel_tuple_cost; parallel_tuple_cost --------------------- 0.1 db # SHOW parallel_setup_cost; parallel_setup_cost --------------------- 1000 |