本節方法適用於 PostgreSQL 8.0 及之後的版本。

取得執行計畫#

在 SQL 敘述前加上 explain 即可取得執行計畫。但有一項重要限制：帶繫結參數（$1、$2 等）的敘述無法直接 explain，必須先 prepare：

PREPARE stmt(int) AS SELECT $1;
EXPLAIN EXECUTE stmt(1);

PostgreSQL 使用 $n 作為繫結參數。你的資料庫抽象層可能把它隱藏起來，讓你能使用 SQL 標準定義的問號。

計畫建立的時機在不同版本間有差異：

  • 至 PostgreSQL 9.1 為止：執行計畫在 prepare 時就已建立，因此無法考慮 execute 提供的實際值。
  • PostgreSQL 9.2 起：計畫的建立延後到執行時，因而能考慮繫結參數的實際值。

不帶繫結參數的敘述可以直接 explain（EXPLAIN SELECT 1;），此時最佳化工具在規劃時一律考慮了實際值。若你使用 9.1 或更早版本、且程式中使用繫結參數，也應該用帶繫結參數的 explain，才能取得相同的執行計畫。

輸出格式如下：

                QUERY PLAN
------------------------------------------
 Result (cost=0.00..0.01 rows=1 width=0)

資訊與本書所用的 Oracle 執行計畫類似：操作名稱、相關成本、列數估算、預期的列寬度。

注意 PostgreSQL 顯示兩個成本值：第一個是啟動成本，第二個是「取回所有列」的總執行成本。Oracle 的執行計畫只顯示第二個值。

explain 的兩個選項#

VERBOSE：提供額外資訊，例如完整限定的資料表名稱——通常價值不高。

ANALYZE：執行敘述並記錄實際耗時與列數，對追查錯誤的基數估算（列數估算）非常有價值。

這對 select 大致無害，但用在寫入敘述上就會修改你的資料。作者建議不要養成自動加上 ANALYZE 的習慣；若要用，可把它包在交易中，事後 rollback：

BEGIN;
EXPLAIN ANALYZE EXECUTE stmt(1);
                   QUERY PLAN
--------------------------------------------------
 Result (cost=0.00..0.01 rows=1 width=0)
         (actual time=0.002..0.002 rows=1 loops=1)
 Total runtime: 0.020 ms
ROLLBACK;

最後別忘了關閉 prepared statement：

DEALLOCATE stmt;

操作#

索引與資料表存取#

  • Seq Scan：以磁碟儲存順序掃描整個關聯（資料表），相當於 TABLE ACCESS FULL。

  • Index Scan：做 B-tree 走訪、走過葉節點找出所有匹配條目，並抓取對應的資料表資料——相當於 INDEX RANGE SCAN 加 TABLE ACCESS BY INDEX ROWID。所謂的索引篩選述詞經常在此造成效能問題。

  • Index Only Scan（9.2 起）：做 B-tree 走訪並走過葉節點，不需要資料表存取，因為索引已具備滿足查詢的所有欄位（例外：MVCC 可見性資訊）。

  • Bitmap Index Scan / Bitmap Heap Scan / Recheck Cond：

    一般的 Index Scan 每次從索引取出一個 tuple 指標，並立刻造訪表中的該 tuple。Bitmap scan 則一口氣從索引取出所有 tuple 指標，用記憶體中的「bitmap」資料結構排序它們，然後依實體 tuple 位置的順序造訪表中的 tuple。

    ——Tom Lane

Join#

join 一次只處理兩張表；查詢中有更多 join 時依序執行。因此「表」也可能意指「中間結果」。

  • Nested Loops：從一張表取得結果，再為每一列查詢另一張表。
  • Hash Join / Hash：把 join 一側的候選記錄載入雜湊表（計畫中標示為 Hash），再以另一側的每筆記錄探測。
  • Merge Join：像拉鍊一樣合併兩份已排序的清單，兩側都必須預先排序。

排序與分組#

  • Sort / Sort Key：依 Sort Key 所列欄位排序。需要大量記憶體具體化中間結果（非管線化）。
  • GroupAggregate：對已排序的集合依 group by 彙總。不緩衝大量資料（管線化）。
  • HashAggregate：用暫時雜湊表分組。不要求輸入已排序，但需要大量記憶體具體化中間結果（非管線化），且輸出沒有任何有意義的排序。

Top-N 查詢#

  • Limit：抓到所需列數時中止底層操作。其效率取決於底層操作的執行模式——中止 Sort 這類非管線化操作時效率極差。
  • WindowAgg：代表使用了視窗函式。

區分存取述詞與篩選述詞#

PostgreSQL 同樣用三種方式套用 where 子句：

  • 存取述詞（Index Cond）：葉節點走訪的起訖條件。
  • 索引篩選述詞（也是 Index Cond）：只在葉節點走訪期間套用，不縮小掃描範圍。
  • 資料表層級篩選述詞（Filter）：欄位不屬於索引，必須先從堆積表載入該列才能求值。

換言之：PostgreSQL 的 explain plan 沒有提供足夠資訊來找出索引篩選述詞。

而顯示為 Filter 的述詞永遠是資料表層級篩選述詞——即使它出現在 Index Scan 操作之下。

範例#

取自「效能與擴展性」一章：

CREATE TABLE scale_data (
   section NUMERIC NOT NULL,
   id1     NUMERIC NOT NULL,
   id2     NUMERIC NOT NULL
);
CREATE INDEX scale_data_key ON scale_data(section, id1);

以下查詢篩選 ID2，而該欄位不在索引中：

PREPARE stmt(int) AS SELECT count(*)
                       FROM scale_data
                      WHERE section = 1
                        AND id2 = $1;
EXPLAIN EXECUTE stmt(1);
                      QUERY PLAN
-----------------------------------------------------
Aggregate (cost=529346.31..529346.32 rows=1 width=0)
  Output: count(*)
  -> Index Scan using scale_data_key on scale_data
     (cost=0.00..529338.83 rows=2989 width=0)
     Index Cond: (scale_data.section = 1::numeric)
     Filter: (scale_data.id2 = ($1)::numeric)

ID2 述詞顯示為 Index Scan 之下的 Filter。這是因為 PostgreSQL 把資料表存取包在 Index Scan 操作內——Oracle 的 TABLE ACCESS BY INDEX ROWID 在 PostgreSQL 中是隱藏在 Index Scan 裡的。因此 Index Scan 有可能對「不在索引中」的欄位做篩選。

換成「效能與擴展性」一章的那個索引後：

CREATE INDEX scale_slow
          ON scale_data (section, id1, id2);
                      QUERY PLAN
------------------------------------------------------
Aggregate (cost=14215.98..14215.99 rows=1 width=0)
  Output: count(*)
  -> Index Scan using scale_slow on scale_data
     (cost=0.00..14208.51 rows=2989 width=0)
     Index Cond: (section = 1::numeric AND id2 = ($1)::numeric)

所有欄位都顯示為 Index Cond——不論它們是存取述詞還是篩選述詞，計畫中不再出現任何 filter 條件。

但請注意：ID2 上的條件無法縮小葉節點走訪範圍，因為索引中 ID1 排在 ID2 之前。實際行為是：Index Scan 掃過 SECTION=1::numeric 的完整範圍，再對每一列套用 ID2=($1)::numeric 篩選。

Backward Oracle Database SQL Server Forward