PostgreSQL從小白到專家，是從入門逐漸能力提升的一個系列教程，內容包括對PG基礎的認知、包括安裝使用、包括角色權限、包括維護管理、、等內容，希望對熱愛PG、學習PG的同學們有幫助，歡迎持續關注CUUG PG技術大講堂。

第29講：執行計劃與成本估算

內容1 : PostgreSQL中查詢執行流程

內容2 : 全表掃描成本估算

內容3 : 索引掃描成本估算

概述

· SQL語句執行五步驟

    Parser

    解析器生成一個解析樹，后續子系統可以從純文本的SQL語句中讀取該樹。

    Analyzer/Analyser

    分析器/對解析器生成的解析樹運行語義分析，并生成查詢樹。

    Rewriter

    重寫器是實現規則系統的系統，必要時根據pg_rules系統目錄中存儲的規則轉換查詢樹。

    PostgreSQL中的視圖是通過規則系統實現的。通過“創建視圖”命令定義視圖時，將自動生成相應的規則并將其存儲在目錄中。              

    假設已經定義了以下視圖并且相應的規則存儲在pg_rules系統目錄中。

CREATE VIEW employees_list 

AS SELECT e.id, e.name, d.name AS department 

FROM employees AS e, departments AS d 

WHERE e.department_id = d.id;

Planner and Executor

    規劃器從重寫器接收查詢樹，并生成（查詢）計劃樹，執行者可以最有效地處理該樹。

    pg_hint_plan插件

    PostgreSQL不支持SQL中的計劃器提示，并且永遠不會支持它。如果要在查詢中使用提示，需要引用pg_hint_plan擴展插件。

執行計劃

· Explain顯示sql執行計劃

與其他RDBMS一樣，PostgreSQL中的explan命令顯示計劃樹本身。

例如：

testdb=# EXPLAIN SELECT * FROM tbl_a WHERE id < 300 ORDER BY data;

QUERY PLAN                           

---------------------------------------------------------------

Sort  (cost=182.34..183.09 rows=300 width=8)

Sort Key: data

  ->  Seq Scan on tbl_a  (cost=0.00..170.00 rows=300 width=8)

    Filter: (id < 300)

(4 rows)

執行器與緩沖區關系

執行器、緩沖區管理器和臨時文件之間的關系

單表查詢成本估算

· 單表查詢中的成本估算

    優化基于成本。成本是無量綱值，這些不是絕對的績效指標，而是比較運營相對績效的指標。

    執行者執行的所有操作都具有相應的成本函數。

    三種成本：啟動、運行和總計。總成本是啟動和運行成本的總和

    啟動成本是在獲取第一個行之前花費的成本。例如，索引掃描節點的啟動成本是讀取索引頁面以訪問目標表中的第一個元組的成本。

    運行成本是獲取所有行的成本。

    總成本是啟動和運行成本的成本之和。

· 單表查詢中的成本估算

    EXPLAN命令顯示每個操作中的啟動和總成本。最簡單的例子如下所示：

testdb=# EXPLAIN SELECT * FROM tbl;

QUERY PLAN 

---------------------------------------------------------

Seq Scan on tbl (cost=0.00..145.00 rows=10000 width=8)

    在第4行中，命令顯示有關順序掃描的信息。在“成本”部分中，有兩個值：0.00和145.00。在這種情況下，啟動和總成本分別為0.00和145.00。

單表查詢成本估算之順序掃描

· Sequential Scan成本計算

    順序掃描的成本由cost_seqscan（）函數估算。我們將探討如何估算以下查詢的順序掃描成本。

testdb=# SELECT * FROM tbl WHERE id < 8000;

在順序掃描中，啟動成本等于0，運行成本由以下等式定義：

· Sequential Scan成本計算

    查詢表的塊數（page）和行數（tuple）：

根據（1，2）得出

‘run cost’=(0.01+0.0025)×10000+1.0×45=170.0

總成本： 

‘total cost’=0.0+170.0=170

· Index Scan成本估算

    計算下面的查詢語句通過索引訪問成本計算：

testdb=# SELECT id, data FROM tbl WHERE data < 240;

    先查詢索引的行數和頁數N_(index,tuple)   N_(index,page)

· IndexScan 成本估算

    啟動成本計算公式

    H_index指的是索引的高度

    啟動成本計算結果：

· IndexScan成本估算

    運行成本計算公式

    索引掃描的運行成本是表和索引的cpu成本和IO（輸入/輸出）成本之和

‘run cost’=(‘index cpu cost’+‘table cpu cost’)+(‘index IO cost’+‘table IO cost’)

    前三個成本（即索引cpu成本，表cpu成本和索引IO成本）計算公式：

· Selectivity

    表的每一列的MCV(Most Common Value)作為一對most_common_vals和most_common_freqs的列存儲在pg_stats視圖中。

    most_common_vals（最常見的的值）是統計MCVs列表的列。             

    most_common_freqs（最常見值的頻率）是統計mcv的頻率列。

mydb=# \x

Expanded display is on.

mydb=# SELECT most_common_vals, most_common_freqs 

             FROM pg_stats 

             WHERE tablename = 'countries' AND attname='continent';

-[ RECORD 1 ]-----+---------------------------------------------------------------------

most_common_vals  | {Africa,Europe,Asia,"North America",Oceania,"South America"}

most_common_freqs | {0.2746114,0.24352331,0.22797927,0.119170986,0.07253886,0.062176164}

· Selectivity

    讓我們考慮下面的查詢，它有一個WHERE子句，“contain=”Asia'：

testdb=# SELECT * FROM countries WHERE

continent = 'Asia';

SELECT continent, count(*) AS "number of countries", 

(count(*)/(SELECT count(*) FROM countries)::real) AS "number of countries / all countries"

FROM countries GROUP BY continent ORDER BY "number of countries" DESC;

 continent   | number of countries | number of countries / all countries 

---------------+---------------------+-------------------------------------

 Africa        |                  53 |                 0.27461139896373055

 Europe        |                  47 |                 0.24352331606217617

 Asia          |                  44 |                 0.22797927461139897

 North America |                  23 |                 0.11917098445595854

 Oceania       |                  14 |                 0.07253886010362694

 South America |                  12 |                 0.06217616580310881

· Selectivity

    總結：

    與“亞洲”對應的最常見頻率值為0.227979。因此，在該估計中使用0.227979作為選擇性。

    對于列值可選項很高的情況，就不能使用MCV，則使用目標列的直方圖界限值來估計成本。

· histogram_bounds

    是一個值列表，用于將列的值分成大致相等的總體組

· Buckets and histogram_bounds

testdb=# SELECT histogram_bounds 

  FROM pg_stats 

  WHERE tablename = 'tbl' AND attname = 'data';

    默認情況下，直方圖界限被劃分為100個桶。上面查詢說明了這個例子中的桶和相應的直方圖范圍。bucket從0開始編號，每個bucket存儲（大約）相同數量的元組。直方圖界限的值是相應存儲桶的界限。例如，直方圖上界的第0個值是1，這意味著它是存儲在bucket_0中的元組的最小值；第1個值是100，這是存儲在bucket_1中的元組的最小值，依此類推。

· Selectivity

    WHERE data<240計算選擇性

· IndexScan成本估算

    前三個成本（即索引cpu成本，表cpu成本和索引IO成本）計算公式：

    根據（1，3，4，6）索引cpu成本、表cpu成本和索引IO成本計算結果：

‘index cpu cost’=0.024×10000×(0.005+0.0025)=1.8,    （7）

‘table cpu cost’=0.024×10000×0.01=2.4,      （8）

‘index IO cost’=ceil(0.024×30)×4.0=4.0.        （9）

· IndexScan成本估算

    table IO cost計算公式：

· IndexScan成本估算

    max_IO_cost計算公式與結果：

    min_IO_cost計算公式與結果：

· indexCorrelation

indexCorrelation=1.0                    （12）

根據（10，11，12）得出： 

‘table IO cost’=180.0+〖1.0〗^2×(5.0?180.0)=5.0      （13）

根據（7，8，9，13）得出索引訪問總成本：

‘run cost’=(1.8+2.4)+(4.0+5.0)=13.2                 （14）

· 列的indexCorrelation查詢

testdb=# \d tbl_corr

    Table "public.tbl_corr"

  Column  |  Type   | Modifiers 

----------+---------+-----------

 col      | text    |

 col_asc  | integer |

 col_desc | integer |

 col_rand | integer |

 data     | text    |

Indexes:

"tbl_corr_asc_idx" btree (col_asc)

"tbl_corr_desc_idx" btree (col_desc)

"tbl_corr_rand_idx" btree (col_rand)

testdb=# select * from tbl_corr;

   col    | col_asc | col_desc | col_rand | data 

----------+---------+----------+----------+------

 Tuple_1  |       1 |       12 |        3 |

 Tuple_2  |       2 |       11 |        8 |

 Tuple_3  |       3 |       10 |        5 |

 Tuple_4  |       4 |        9 |        9 |

 Tuple_5  |       5 |        8 |        7 |

 Tuple_6  |       6 |        7 |        2 |

 Tuple_7  |       7 |        6 |       10 |

 Tuple_8  |       8 |        5 |       11 |

 Tuple_9  |       9 |        4 |        4 |

 Tuple_10 |      10 |        3 |        1 |

 Tuple_11 |      11 |        2 |       12 |

 Tuple_12 |      12 |        1 |        6 |

(12 rows)

· indexCorrelation與表之間的關系

· 列的indexCorrelation查詢

testdb=# SELECT tablename,attname, correlation FROM pg_stats 

WHERE tablename = 'tbl_corr';

tablename | attname  | correlation 

-----------+----------+-------------

tbl_corr  | col_asc  |           1

tbl_corr  | col_desc |          -1

tbl_corr  | col_rand |    0.125874

· 總成本

根據（5，14），得出通過索引訪問表的總代價：

（5）--啟動成本

（14）--通過索引訪問表的成本

        ‘total cost’=0.285+13.2=13.485          （15）

testdb=# EXPLAIN SELECT id, data FROM tbl WHERE data < 240;

QUERY PLAN                                 

---------------------------------------------------------------------------

Index Scan using tbl_data_idx on tbl  (cost=0.29..13.49 rows=240 width=8)

   Index Cond: (data < 240)

· seq_page_cost and random_page_cost相關參數配置

HDD硬盤：

seq_page_cost=1.0

random_page_cost=4.0

SSD硬盤：

seq_page_cost=1.0  

random_page_cost=1.0

單表查詢成本估算之排序

· Sort

    成本估算公式：

    估算以下查詢語句排序成本：

testdb=# SELECT id, data FROM tbl WHERE data < 240 ORDER BY id;

· Sort成本估算

-->> 往期公開課資料，聯系CUUG客服領取

以上就是【PostgreSQL從小白到專家】第29講 -執行計劃與成本估算 的內容，歡迎進群一起探討交流

釘釘交流群：35822460，釘釘群專門有視頻講解