診斷重複的 PostgreSQL 查詢時，請將候選項的建立與候選項的使用分開檢視。查看內部掃描的執行次數，確認快取是否避免了重複工作，接著比較分組重用、CTE 內聯或有索引的暫存表。

本逐步指南適用於使用 Mac、需要說明一個緩慢文章查詢的開發者：先選取一次候選文章，再找出每篇文章所屬主題中 ID 最高的候選文章。全程使用合成資料列。目標是找出工作重複之處，並選擇能保留查詢結果的查詢形式。

設定可重現的候選查詢

使用已有的本機 PostgreSQL 安裝，並確保可使用 psql。開啟 Terminal，使用你平常的連線設定執行 psql，並在整個 SQL 範例過程中保持同一個工作階段開啟。PostgreSQL 文件將 psql 說明為可用來提交查詢和查看結果的互動式終端機。psql

先用 SHOW server_version; 查看伺服器版本。本文查閱的最新文件指出，PostgreSQL 18 是目前版本。範例使用 PostgreSQL 12 或更新版本的語法；這是功能適用版本的界線，不代表建議使用較舊版本。PostgreSQL 12 引入了 CTE 內聯，以及 MATERIALIZED 和 NOT MATERIALIZED 控制項。WITH Queries、Release 12

在全新的工作階段中執行一次以下設定。它會建立 20,000 篇合成文章，以及一個供主題和 ID 查詢使用的索引。下方使用的候選條件會選取前 2,000 篇文章。

CREATE TEMP TABLE article_demo (
    id integer PRIMARY KEY,
    topic_id integer NOT NULL,
    title text NOT NULL
);

INSERT INTO article_demo (id, topic_id, title)
SELECT g, 1 + (g % 50), 'Article ' || g
FROM generate_series(1, 20000) AS s(g);

CREATE INDEX article_demo_topic_id_idx
    ON article_demo (topic_id, id);

ANALYZE article_demo;

這些暫存表預設會在工作階段結束時消失。載入資料後，請明確執行 ANALYZE：PostgreSQL 的 autovacuum 程序不會分析暫存表。CREATE TABLE

接著記錄基準結果。LATERAL 子查詢會計算目前文章所屬主題中的最高候選 ID。此處的「最高 ID」只是所定義的任務，並不代表出版日期。

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate AS x
    WHERE x.topic_id = c.topic_id
) AS m;

明確使用 MATERIALIZED 可清楚劃定實驗中的重用邊界。具體化的 CTE 可以只建立一次，而其使用端會反覆讀取儲存的資料列。PostgreSQL 文件舉例說明，多次參照的具體化 CTE 在搜尋該中間結果時，會失去底層資料表索引帶來的效益。Common Table Expression Materialization

本文已檢視 SQL 範例，但未在 PostgreSQL 伺服器上執行。合成資料的資料列數量計算已另外核對；本文沒有把任何執行時間或擷取的查詢計畫當成測量結果呈現。

閱讀查詢計畫以定位重複工作

從產生候選項的分支開始，再追蹤每個使用端。在巢狀迴圈下，找出外部輸入和內部查詢。PostgreSQL 以 loops 表示節點的執行次數；實際資料列數和節點時間則是每次執行的平均值。若使用相關純量子查詢，重複工作也可能顯示在 SubPlan 下，因此診斷時應追蹤實際執行的節點，而不必要求特定標籤。Using EXPLAIN

從實際查詢計畫中記錄四項觀察：

  1. 有多少資料列進入外部查詢？
  2. 底層內部掃描執行了幾次？
  3. 該掃描是否讀取候選項後，又透過篩選器丟棄大部分資料列？
  4. 外部資料列與耗時較高的內部工作之間是否有 Memoize 節點？

請區分不同層級。彙總節點可能每次執行只回傳一個最大值，但其子節點可能掃描許多資料列來計算它。因此，候選項產生器只執行一次，並不能證明候選項的使用成本很低。

在這個範例中，2,000 個候選項分布於 50 個主題，每個主題有 40 篇文章。以下計算說明可能的工作模式，不代表保證會出現的查詢計畫輸出或執行時間：

使用模式 候選資料列檢查數量示例
每篇外部文章都完整掃描候選集合 2,000 × 2,000 = 4,000,000
每個主題完整掃描一次，並快取之後的所有查詢 50 × 2,000 = 100,000
依主題分組候選項一次 2,000 列進入分組步驟，另加後續連接工作

這項區別在 PostgreSQL 14 及更新版本中特別重要，因為該版本引入了內部巢狀迴圈結果的備忘錄快取。當前文件將 Memoize 描述為會快取帶參數的掃描結果，並在快取命中時略過底層掃描；快取項目也可能被逐出。在將數千筆外部資料列歸因於數千次完整掃描之前，請檢查快取鍵，以及回報的命中、未命中、逐出和溢位情況。Release 14、Query Planning

也請在最早出現明顯落差的地方，比較估計資料列數與實際資料列數。PostgreSQL 建議檢查這些估計值，並提醒玩具資料表的查詢計畫未必能推演至較大的輸入規模。避免將父節點和子節點的時間相加：它們描述的是重疊的執行工作。Using EXPLAIN

選擇符合重複工作需求的改寫方式

關鍵是每個使用端需要從候選集合中取得多少工作成果。用以下判斷表選擇第一個實驗：

觀察到的需求 第一個實驗 必須保留的條件
每個候選項都需要相同的主題最高值 先分組，再連接 每個主題恰有一筆摘要資料列
每個使用端只需要來源中一小部分有索引的資料 嘗試 NOT MATERIALIZED 保留完整的候選條件
多個陳述式需要明確建立索引的候選關聯 建立有索引的暫存表 使用同一個工作階段，並計入設定成本
重複參數已有許多快取命中 改變查詢形式前，先比較總執行時間 確認底層掃描確實耗費大量成本

一次計算每個主題的結果

對這項任務而言，分組摘要能直接表達重用方式。完成設定後，在同一個工作階段執行以下內容：

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
WITH candidate AS MATERIALIZED (
    SELECT id, topic_id, title
    FROM article_demo
    WHERE id <= 2000
), topic_max AS (
    SELECT topic_id, max(id) AS highest_id
    FROM candidate
    GROUP BY topic_id
)
SELECT c.id, c.title, m.highest_id
FROM candidate AS c
JOIN topic_max AS m USING (topic_id);

此改寫會為每個主題產生一個最高值，並將它附加到每篇候選文章上。PostgreSQL 將 max 定義為非空輸入值中的最大值。範例中的主題和 ID 欄位皆不為空，因此等價關係很直接：每個候選項都屬於一個包含自身的群組。Aggregate Functions

若不執行 EXPLAIN，預期查詢會回傳 2,000 列。例如，文章 1 屬於主題 2，會取得 highest_id = 1951；主題 1 則會取得 highest_id = 2000。這些結果可由範例產生器確定地推導出來。

在推廣這種做法前，先確認結果的意義。依額外欄位分組可能會讓每個主題產生多筆摘要資料列，進而使連接輸出倍增。若主題 ID 允許為 null，也需要重新檢視等值比較的語意。如果任務需要的是最高 ID 文章的標題，還要把最大值連回候選資料列；另外再取標題的最大值，回答的是另一個問題。

這種改寫允許使用不同的查詢計畫，但不強制採用雜湊連接，也不保證會加速。請將實際測得的分組和連接工作，與基準查詢實際執行的內部掃描次數比較。

測試來源索引能否取代中間掃描

若 CTE 符合條件，請在完整的基準查詢中將 AS MATERIALIZED 替換為 AS NOT MATERIALIZED，然後重新執行。這會允許查詢與來源關聯共同最佳化，可能以來源查詢取代對已儲存候選項的掃描，但也可能重複計算候選項。Common Table Expression Materialization

範例中的 (topic_id, id) 索引，適用於對前導主題欄位進行相等比對，以及對 ID 欄位進行限制。因此，這是值得測試的合理索引；請確認實際查詢計畫是否使用它，以及掃描了多少資料。PostgreSQL 文件指出，多欄 B-tree 索引在前導欄位使用相等條件、並在下一欄使用不等條件時，可縮小掃描範圍。Multicolumn Indexes

請將 id <= 2000 保留在候選項定義中。若在全部 20,000 筆來源資料中查詢最大值，就會改變答案。也請遵守文件所述的適用範圍：遞迴 CTE 和非純函式 CTE 會忽略 NOT MATERIALIZED。對於不符合條件的 CTE，請改用受支援的關聯式改寫，或明確暫存其結果。SELECT

多個陳述式重用時，暫存有索引的候選關聯

CTE 的作用範圍僅限單一陳述式。如果多個陳述式需要相同的候選資料列和明確索引，暫存表可提供不同的生命週期和索引選項。完成原始設定後，執行以下替代方案：

CREATE TEMP TABLE candidate_demo AS
SELECT id, topic_id, title
FROM article_demo
WHERE id <= 2000;

CREATE INDEX candidate_demo_topic_id_idx
    ON candidate_demo (topic_id, id);

ANALYZE candidate_demo;

EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT c.id, c.title, m.highest_id
FROM candidate_demo AS c
CROSS JOIN LATERAL (
    SELECT max(x.id) AS highest_id
    FROM candidate_demo AS x
    WHERE x.topic_id = c.topic_id
) AS m;

暫存表的索引同樣是暫存的，而關聯的作用範圍則限於工作階段。評估暫存是否有助於完成整項任務時，請將建立資料表、建立索引和分析都納入考量。不要只拿最終查詢與未暫存時的整體操作相比。CREATE TABLE

在 Mac 上用 node-postgres 記錄相同實驗

若要檢查應用程式的查詢路徑，請使用目前受支援的 Node.js 版本，並透過 npm install pg 安裝文件所述的套件。在該專案中將以下完整程式儲存為 diagnose.mjs，並執行 node diagnose.mjs。它會自行建立合成輸入，不依賴先前的 psql 工作階段。node-postgres

程式會使用你現有的 PostgreSQL 連線環境。node-postgres 文件列出的連線環境變數與 psql 使用的相同，包括 PGHOST、PGPORT 和 PGDATABASE。Connecting

import pg from 'pg';
const { Client } = pg;
const client = new Client();

try {
  await client.connect();
  const version = await client.query('SHOW server_version');
  console.log(version.rows[0]);

  await client.query(`
    CREATE TEMP TABLE article_demo (
      id integer PRIMARY KEY,
      topic_id integer NOT NULL,
      title text NOT NULL
    );
    INSERT INTO article_demo (id, topic_id, title)
    SELECT g, 1 + (g % 50), 'Article ' || g
    FROM generate_series(1, 20000) AS s(g);
    CREATE INDEX article_demo_topic_id_idx
      ON article_demo (topic_id, id);
    ANALYZE article_demo;
  `);

  const sql = `
    WITH candidate AS MATERIALIZED (
      SELECT id, topic_id, title
      FROM article_demo
      WHERE id <= $1::integer
    )
    SELECT c.id, c.title, m.highest_id
    FROM candidate AS c
    CROSS JOIN LATERAL (
      SELECT max(x.id) AS highest_id
      FROM candidate AS x
      WHERE x.topic_id = c.topic_id
    ) AS m
  `;

  const result = await client.query({
    text: `EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS) ${sql}`,
    values: [2000]
  });

  for (const row of result.rows) {
    console.log(row['QUERY PLAN']);
  }
} catch (error) {
  console.error(error);
  process.exitCode = 1;
} finally {
  await client.end();
}

查詢設定使用文件所述的 text 和 values 欄位。新增 name 會在每個連線上建立一個預備陳述式；它不會快取候選資料列，也不會消除內部的重複執行。Queries

若現有應用程式使用連線池，請在建立和使用暫存表時，使用同一個已取得的用戶端，然後在 finally 中釋放它。不同的 pool.query 呼叫可能會使用不同用戶端。同樣地，node-postgres 要求交易中的所有陳述式都使用同一個用戶端執行個體。Pooling、Transactions

對於行為會隨參數值改變的命名陳述式，請另外調查查詢規劃。PostgreSQL 可以選擇通用計畫或參數專屬的自訂計畫；文件所述的檢查方式是對預備陳述式執行 EXPLAIN EXECUTE。因此，剛對未命名查詢執行的說明結果，可能與已建立的預備陳述式執行結果不同。PREPARE

判斷重複工作是否真的減少

每次比較都使用相同的候選條件、選取欄位和輸入值。先執行一輪，再重複執行可比較的輪次，以觀察快取變化。記錄伺服器版本、候選數量、內部掃描執行次數、快取行為和總執行時間。PostgreSQL 17 改進了 CTE 規劃，會使用欄統計和排序資訊，因此比較不同機器的結果時，請一併記錄版本。Release 17

第二輪診斷時，EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) 會保留資料列數和陳述式執行時間，同時避免逐節點的時鐘測量。父節點的緩衝區總數包含子節點活動，因此不要將整棵樹的數字相加。EXPLAIN ANALYZE 會執行查詢，但不會測量將結果資料列傳送至 Node.js 的時間。EXPLAIN

將規劃器開關視為實驗。關閉 enable_nestloop 會抑制巢狀迴圈，但無法完全排除它；enable_material 也無法抑制所有具體化。這兩種開關都不能證明候選項使用已變得有效率。Query Planning

最後選擇能保留所需資料列，並減少整項任務實測工作量的查詢形式。對於這種逐主題求最大值的需求，分組重用是最清楚的初步實驗。若有索引的查詢證明成本很低，就保留它；若額外設定能在多個使用端反覆使用時帶來效益，就明確暫存。

來源