示範資料庫#

本書前幾篇的範例都建立在只有寥寥數列的簡單表格上。查詢執行的討論在這方面要求更高：我們需要有關聯、且列數多得多的表格。

作者採用既有的示範資料庫（demo database），它描繪俄羅斯的旅客航空運輸，使用的是 2017 年 8 月 15 日建立的較大版本。

開發這個示範資料庫時，作者群刻意讓 schema 簡單到不需額外說明就能理解，同時又複雜到足以撰寫有意義的查詢。資料庫填入的是貼近真實的資料，讓範例更完整，處理起來也更有意思。

資料庫 schema 概覽

主要實體是訂位（bookings 表格）：

• 一筆訂位可包含多名乘客，每人一張電子機票（tickets）。乘客本身不構成獨立實體；本書實驗假設所有乘客都是唯一的。
• 每張機票包含一或多個航段（ticket_flights）。單張機票有多個航段有兩種情況：來回票，或轉機票。schema 中雖無對應約束，但假設同一筆訂位中的所有機票具有相同航段。
• 每個航班（flights）從一個機場（airports）飛往另一個。航班號相同者出發地與目的地相同，但出發日期不同。
• routes 視圖建立在 flights 表格之上，顯示與特定航班日期無關的航線資訊。
• 報到時每位乘客拿到帶座位號的登機證（boarding_passes）。只有該航班包含在機票中才能報到；航班－座位組合必須唯一，因此不可能為同一座位發出兩張登機證。
• 飛機座位數（seats）與艙等分佈取決於執飛的機型（aircrafts）。假設每種機型只有一種座艙配置。

有些表格使用代理主鍵，有些使用自然鍵（部分為複合鍵）。這純粹是為了示範，絕非值得效法的範例。

示範資料庫可視為真實系統的一份 dump：它包含過去某個時間點的資料快照。要顯示那個時間，可呼叫 bookings.now() 函式——在現實中會用 now() 的示範查詢中，請改用它。

機場、城市與機型名稱存放在 airports_data 與 aircrafts_data 表格中，提供英語與俄語兩種語言；範例通常查詢 airports 與 aircrafts 視圖，它們依 bookings.lang 參數值選擇輸出語言。不過查詢計畫中仍可能出現基表的名稱。

簡單查詢協定#

簡單版本的主從協定讓 SQL 查詢得以執行：它把查詢文字送到伺服器，並取回完整的執行結果，不論結果有多少列。

送到伺服器的查詢會經過四個階段：剖析（parse）→ 轉換（transform）→ 規劃（plan）→ 執行（execute）。

剖析#

首先 PostgreSQL 必須剖析查詢文字，才能理解要執行什麼。

詞法與語法分析：

• lexer 把查詢文字切分成一組 lexeme（關鍵字、字串字面值、數值字面值等）
• parser 依 SQL 語言文法驗證這組 lexeme

PostgreSQL 依賴標準剖析工具，即 Flex 與 Bison。剖析後的查詢在 backend 記憶體中以抽象語法樹（abstract syntax tree）表示。

以下列查詢為例：

SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = 'postgres'
ORDER BY tablename;

lexer 挑出五個關鍵字、五個識別字、一個字串字面值與三個單字元 lexeme（逗號、等號、分號）。parser 用這些建出剖析樹，節點包括 TARGETENTRY（選取欄位）、FROMEXPR（FROM 與 WHERE）、SORTGROUPCLAUSE（ORDER BY）、RTE、OPEXPR 等。

略顯晦澀的縮寫 RTE 代表 Range Table Entry。PostgreSQL 原始碼用 range table 一詞指涉表格、子查詢、連接結果——換言之，任何可被 SQL 運算子處理的列集合。

語意分析的目的是查明：

• 資料庫中是否存在該查詢以名稱指涉的表格或其他物件
• 使用者是否有權存取這些物件

語意分析所需的全部資訊都存放在系統目錄中。語意分析器收到剖析樹後會進一步重構它，加入對特定資料庫物件、資料型別與其他資訊的參照。

啟用 debug_print_parse 參數可在伺服器日誌中檢視完整剖析樹，但實用意義不大。

轉換#

下一階段，查詢可能被轉換（重寫）。PostgreSQL 核心把轉換用於幾個目的：

• 把剖析樹中的視圖名稱換成該視圖基礎查詢所對應的子樹
• 實作列層級安全性（row-level security）
• 遞迴查詢的 SEARCH 與 CYCLE 子句也在此階段被轉換（PostgreSQL 14 起）

上例中 pg_tables 是視圖；若把它的定義放進查詢文字，等價於一個含 pg_class LEFT JOIN pg_namespace LEFT JOIN pg_tablespace 的子查詢。

但伺服器並不處理查詢的文字表示，所有操作都在剖析樹上進行。（啟用 debug_print_rewritten 可在伺服器日誌檢視完整的轉換後樹。）

剖析樹反映的是查詢的語法結構，它完全沒有說明操作該以什麼順序執行。

PostgreSQL 也支援使用者透過重寫規則系統（rewrite rule system）實作的自訂轉換。

規劃#

SQL 是宣告式語言：查詢指定要取哪些資料，卻不指定如何取。

任何查詢都有多條執行路徑。剖析樹中的每個操作都能以多種方式完成：

• 結果可以透過讀取整張表（再濾掉多餘者）取得，也可以透過索引掃描找出所需列
• 資料集永遠是成對連接的，因此連接順序的組合數量龐大
• 此外還有多種連接演算法：掃描第一個資料集的列並在另一集合中尋找匹配列，或先把兩個資料集排序再合併

每種演算法都能找到「它表現得比其他演算法更好」的使用情境。而最佳與非最佳計畫的執行時間可能相差好幾個數量級——因此負責最佳化的規劃器是系統中最複雜的元件之一。

計畫樹#

執行計畫同樣以樹表示，但它的節點處理的是資料上的實體操作，而非邏輯操作。

=> EXPLAIN SELECT schemaname, tablename
FROM pg_tables
WHERE tableowner = 'postgres'
ORDER BY tablename;
                             QUERY PLAN
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
 Sort (cost=21.03..21.04 rows=1 width=128)
   Sort Key: c.relname
   −> Nested Loop Left Join (cost=0.00..21.02 rows=1 width=128)
       Join Filter: (n.oid = c.relnamespace)
       −> Seq Scan on pg_class c (cost=0.00..19.93 rows=1 width=72)
           Filter: ((relkind = ANY ('{r,p}'::"char"[])) AND (pg_g...
       −> Seq Scan on pg_namespace n (cost=0.00..1.04 rows=4 wid...
(7 rows)

兩點值得注意：

• 樹中只含三張被查詢表格中的兩張：規劃器看出其中一張對取得結果並非必要，於是把它從計畫樹中移除
• 每個節點都附有估算成本與預期處理的列數

想探索完整計畫樹可啟用 debug_print_plan 把它們傾印到伺服器日誌，但實務上檢視 EXPLAIN 顯示的文字表示通常就夠了。

計畫中的 Seq Scan 節點對應讀取表格，Nested Loop 節點則代表連接操作：

計畫搜尋#

PostgreSQL 使用基於成本的最佳化器：它走訪潛在計畫並估算其執行所需資源（I/O 操作、CPU 週期）。這個估算被正規化成一個數值，稱為計畫的成本（cost）；所有考量過的計畫中，成本最低者被選中。

問題是：潛在可用計畫的數量隨連接表格數呈指數成長，因此不可能全部考慮——即使對相對簡單的查詢也是如此。

搜尋通常以動態規劃演算法搭配一些啟發法來收窄，讓規劃器能在可接受時間內為表格數較多的查詢找到數學上精確的解。

管理連接順序#

查詢的寫法可以在某種程度上限縮搜尋範圍（代價是可能錯過最佳計畫）：

• 共用表格運算式（CTE）與主查詢可分別最佳化；要保證此行為可指定 MATERIALIZED 子句
• 非 SQL 函式中執行的子查詢永遠分別最佳化（SQL 函式有時能被內嵌進主查詢）
• 設定 join_collapse_limit 並在查詢中使用明確的 JOIN 子句，部分連接順序就會由查詢的語法結構決定；from_collapse_limit 對子查詢有同樣效果

扁平化（collapsing）機制：

SELECT ... FROM a, b, c, d, e WHERE ...

此處規劃器必須考慮所有可能的連接配對。這個查詢對應的剖析樹片段（示意）如下：

而在下例中：

SELECT ... FROM a, b JOIN c ON ..., d, e WHERE ...

剖析樹反映了 JOINEXPR 的結構：

規劃器通常會把連接樹扁平化，使其看起來像第一個例子：演算法遞迴走訪樹，把每個 JOINEXPR 節點換成其元素的平坦清單。

然而只有在扁平化後的清單元素不超過 join_collapse_limit（預設 8）時才會執行。本例中若 join_collapse_limit 小於 5，JOINEXPR 節點就不會被扁平化，這對規劃器意味著：

• 表格 b 必須與 c 連接（或反之；配對內的順序不受限）
• 表格 a、d、e 與「b 連接 c 的結果」可以任意順序連接

若 join_collapse_limit 設為 1，明確 JOIN 子句所定義的順序就會被完整保留。

from_collapse_limit（預設 8）以類似方式控制子查詢的扁平化。子查詢看起來不像 JOIN 子句，但在剖析樹層級相似性就顯現了——差別只在該樹含的是 FROMEXPR 而非 JOINEXPR（參數名稱由此而來）。

基因查詢最佳化#

扁平化後，樹的同一層可能含有太多元素——不論是表格還是必須分別最佳化的連接結果。規劃時間隨待連接資料集數量呈指數成長，可能超出一切合理界限。

若 geqo 參數啟用（預設 on）且同一層的元素數超過 geqo_threshold（預設 12），規劃器就會改用基因演算法（genetic algorithm）最佳化查詢。

這個演算法比動態規劃版本快得多，但無法保證找到的計畫是最佳的。因此經驗法則是：減少待最佳化的元素數量，避免用到基因演算法。

選擇最佳計畫#

計畫是否算最佳，取決於特定客戶端打算如何使用查詢結果：

• 客戶端需要一次取得完整結果（例如產生報表）→ 計畫應最佳化「取得所有列」
• 優先目標是盡快回傳前幾列（例如顯示在螢幕上）→ 最佳計畫可能完全不同

為做出這個選擇，PostgreSQL 計算成本的兩個分量：

• 啟動成本（startup cost）：為節點執行做準備所付的代價
• 總成本（total cost）：取得結果所產生的全部支出

有時有人說啟動成本是「取得結果集第一列的成本」，但這並不完全準確。

最佳化器會檢查查詢是否使用游標（透過 SQL 的 DECLARE，或在 PL/pgSQL 中明確宣告）：

• 不使用游標 → 假設客戶端一次需要整個結果，選擇總成本最低的計畫
• 使用游標 → 選中的計畫必須最佳化「只取全部列的 cursor_tuple_fraction（預設 0.1）」，即選擇下列運算式值最小的計畫：

啟動成本 + cursor_tuple_fraction × (總成本 − 啟動成本)

成本估算的原理#

要估算計畫的總成本，必須取得其所有節點的成本估算。節點的成本取決於：

• 節點類型（讀取 heap 資料的成本顯然與排序成本不同）
• 該節點處理的資料量（資料量越大，成本通常越高）

節點類型是已知的，但資料量只能依下列兩者推算：

• 輸入集合的基數（cardinality，節點取用的列數）
• 節點的選擇性（selectivity，輸出時剩下的列的比例）

這些計算依賴蒐集到的統計資訊（表格大小、欄位中的資料分佈等）。因此最佳化的成效取決於 autovacuum 所蒐集與更新的統計資料是否正確。

若每個節點的基數估算都準確，算出的成本很可能適當反映實際成本。規劃的主要缺陷通常源自錯誤的基數與選擇性估算——這可能肇因於不準確或過時的統計、無法使用統計，或（影響較小地）規劃模型本身的不完美。

基數估算#

要計算節點的基數，規劃器必須遞迴完成以下步驟：

1. 估算每個子節點的基數，評估該節點會從它們收到多少輸入列
2. 估算該節點的選擇性，也就是輸入列中會留到輸出的比例

節點的基數是這兩個值的乘積。

規劃器首先估算定義資料存取方法的葉節點基數，這些計算依賴蒐集到的統計（如表格總大小）。

過濾條件的選擇性取決於其類型。最單純的情況可假設為常數值，儘管規劃器會盡量利用所有可得資訊來精修估算。一般而言，只要知道如何估算簡單過濾條件就夠了；若條件含邏輯運算，其選擇性由下列公式計算：

sel(x AND y) = sel(x) × sel(y)
sel(x OR y)  = 1 − (1 − sel(x))(1 − sel(y)) = sel(x) + sel(y) − sel(x)sel(y)

遺憾的是，這些公式假設謂詞 x 與 y 彼此獨立。對於相關的謂詞，這類估算會不準確。

要估算連接的基數，規劃器必須取得笛卡兒積的基數（兩個資料集基數的乘積），再估算連接條件的選擇性（同樣取決於條件類型）。

成本估算#

估算成本的過程同樣是遞迴的：要計算子樹的成本，需計算並加總其所有子節點的成本，再加上父節點本身的成本。

估算節點成本時，PostgreSQL 套用該節點所執行操作的數學模型，並以已估好的節點基數為輸入。每個節點都會計算啟動成本與總成本：

• 有些操作沒有前置條件、可立即開始執行，這類節點的啟動成本為零
• 其他操作則需等待某些預備動作完成。例如排序節點通常必須等子節點的全部資料到齊才能進行自己的任務——這類節點的啟動成本通常大於零：即使上層節點（或客戶端）只需要輸出中的一列，這個代價仍得付

規劃器的所有計算只是估算，可能與實際執行時間毫無關係。它們唯一的用途是：在相同條件下比較同一查詢的不同計畫。

其他情況下比較查詢（尤其是不同查詢）的成本毫無意義。例如成本可能因統計過時而被低估；統計更新後算出的數字可能上升——但由於估算變得更準確，伺服器反而會選出更好的計畫。

執行#

規劃期間建立的計畫現在必須被執行。

執行器在 backend 記憶體中開啟一個 portal：這是一個保存「目前執行中查詢狀態」的物件。這個狀態表示為一棵重複計畫樹結構的樹，其節點像生產線一樣運作，彼此請求並傳送列。

查詢執行從根部開始：

• 根節點（本例中代表 SORT 操作）向其子節點拉取資料。收到所有列後排序，再傳給客戶端
• 有些節點（如 NESTLOOP）合併來自不同來源的資料集。這類節點從兩個子節點拉資料，一旦收到滿足連接條件的一對列，就立刻把結果列往上傳（不像排序必須先取得所有列）。此時該節點的執行被中斷，直到父節點請求下一列
• 兩個 SEQSCAN 葉節點負責表格掃描。父節點請求資料時，它們就從對應表格取出下一列

若只需要部分結果（例如查詢中有 LIMIT 子句），操作就不會被完整執行。

因此，有些節點不儲存任何列、立刻往上傳；但另一些（如 SORT）必須保存可能龐大的資料量。為此在 backend 記憶體中配置一塊 work_mem（預設 4 MB）；若不夠，剩餘資料會溢寫到磁碟上的暫存檔。

一個計畫可能有多個需要資料儲存的節點，因此 PostgreSQL 可能配置多塊各為 work_mem 大小的記憶體——一個查詢能使用的 RAM 總量沒有任何限制。

延伸查詢協定#

使用簡單查詢協定時，每個指令（即使重複執行多次）都必須走完全部四個階段。這帶來兩個問題：

• 反覆剖析同一查詢毫無意義；反覆剖析只有常數不同的查詢同樣沒什麼道理——剖析樹結構終究相同
• 客戶端一次收到全部結果，不論它可能包含多少列

一般而言，用 SQL 指令能繞過這些限制：PREPARE + EXECUTE 處理第一點，DECLARE + FETCH 處理第二點。

但這樣一來，新建物件的命名必須由客戶端處理，而伺服器還得承擔剖析額外指令的開銷。

延伸主從協定提供替代方案，讓我們能在協定本身的指令層級精確控制各個運算子執行階段。

預備#

預備階段照常剖析並轉換查詢，但產生的剖析樹被保存在 backend 記憶體中。

缺點顯而易見：每個 backend 都得剖析所有進來的查詢，即使同一查詢已被另一個 backend 剖析過。

但也有好處：全域快取很容易因鎖而成為瓶頸。若某客戶端執行大量小而不同的查詢（例如只有常數不同），會產生大量流量並可能拖累整個實例的效能。在 PostgreSQL 中查詢是本地剖析的，因此對其他行程沒有影響。

預備查詢可以參數化：

=> PREPARE plane(text) AS
SELECT * FROM aircrafts WHERE aircraft_code = $1;

所有具名的預備語句顯示在 pg_prepared_statements 視圖中。

這裡找不到匿名語句（使用延伸查詢協定或 PL/pgSQL 者），其他 backend 預備的語句也不會顯示——無法存取其他 session 的記憶體。

參數綁定#

預備語句執行前必須綁定實際參數值：

=> EXECUTE plane('733');
 aircraft_code |     model      | range
−−−−−−−−−−−−−−−+−−−−−−−−−−−−−−−−+−−−−−−−
 733           | Boeing 737−300 | 4200
(1 row)

相較於把字面值串接進查詢字串，在預備語句中綁定參數的優勢是讓 SQL 注入絕無可能：綁定的參數值無論如何都無法修改已建好的剖析樹。

若不用預備語句而要達到同等安全水準，你就得仔細跳脫每個來自不可信來源的值。

規劃與執行：自訂計畫 vs. 通用計畫#

執行預備語句時，查詢規劃是依實際參數值進行的，然後計畫被交給執行器。

不同的參數值可能意味著不同的最佳計畫，因此把確切的值納入考量很重要：

-- 昂貴的訂位不多 → 用索引
=> EXPLAIN SELECT * FROM bookings WHERE total_amount > 1000000;
 Bitmap Heap Scan on bookings (cost=86.49..9245.82 rows=4395 wid...
   −> Bitmap Index Scan on bookings_total_amount_idx ...

-- 條件涵蓋全部訂位 → 用索引沒意義，整表掃描
=> EXPLAIN SELECT * FROM bookings WHERE total_amount > 100;
 Seq Scan on bookings (cost=0.00..39835.88 rows=2111110 width=21)

某些情況下，伺服器可能同時保留剖析樹與查詢計畫，以避免重複規劃。這種計畫不考慮參數值，因此稱為通用計畫（generic plan），與依實際值建立的自訂計畫（custom plan）相對。

伺服器能無損效能地使用通用計畫的明顯情況，是無參數的查詢。

切換規則：

1. 參數化預備語句的前五次最佳化永遠依實際參數值進行，規劃器據此計算自訂計畫的平均成本
2. 從第六次執行起，若通用計畫平均而言比自訂計畫更有效率（並考量自訂計畫每次都得重建的成本），規劃器就保留通用計畫並持續使用，跳過最佳化階段

實測：前幾次 EXPLAIN EXECUTE 顯示的是實際值（自訂計畫）；第五次執行後切換到通用計畫，EXPLAIN 顯示參數以位置指涉而非以值：

=> EXPLAIN EXECUTE plane('321');
 Seq Scan on aircrafts_data ml (cost=0.00..1.39 rows=1 width=52)
   Filter: ((aircraft_code)::text = $1)
(2 rows)

不難想像一種不幸的發展：前幾個自訂計畫比通用計畫更貴，後續計畫本可更有效率，但規劃器根本不會再考慮它們。此外，它比較的是估算值而非實際成本，這也可能導致誤判。

若規劃器判斷失誤，可透過 plan_cache_mode 參數（預設 auto）覆寫自動決策，強制選用通用或自訂計畫：

=> SET plan_cache_mode = 'force_custom_plan';

pg_prepared_statements 視圖也會顯示所選計畫的統計（generic_plans／custom_plans）。

取得結果#

延伸查詢協定允許分批取得資料，而非一次全拿。SQL 游標效果幾乎相同（差別在伺服器有些額外工作，且規劃器最佳化的是取得前 cursor_tuple_fraction 列而非整個結果集）：

=> BEGIN;
=> DECLARE cur CURSOR FOR
  SELECT * FROM aircrafts ORDER BY aircraft_code;
=> FETCH 3 FROM cur;
=> FETCH 2 FROM cur;
=> COMMIT;

若查詢回傳很多列而客戶端全都需要，系統吞吐量高度取決於批次大小：一批中的列越多，存取伺服器與取得回應所產生的通訊開銷就越少。

但隨著批次變大，這些好處會變得不那麼明顯：逐列取得與每批十列之間的差異可能極為巨大，但比較每批 100 列與 1000 列時就沒那麼顯著了。