歡迎光臨
每天分享高質量文章

PostgreSQL 查詢成本模型

(點擊上方公眾號,可快速關註)


英文: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恤¥搶先預覽(長按複製整段文案,打開手機淘寶即可進入活動內容)

近期,北京地區正常發貨,但派件時間有所延長。

赞(0)

分享創造快樂