PostgreSQL 對 id < 11 可能會使用索引，而對 id > 11 進行循序掃描，因為第一個條件只符合極少數資料列，第二個條件則幾乎符合整張資料表。兩個運算子都支援 B-tree 索引；規劃器會比較它們的預估成本。

本指南協助使用 Mac 的開發者透過合成文章資料列重現這項選擇、讀取計畫，並檢查這種差異是否需要處理。內容依據目前檢索到的 PostgreSQL 18 文件及最新 node-postgres 文件。比較計畫細節前，請先確認伺服器版本；這些範例不保證在不同版本或機器上會產生完全相同的計畫。PostgreSQL：使用 EXPLAIN

為什麼同一個索引會產生不同的掃描選擇？

B-tree 索引支援小於和大於比較。改變比較方向不會讓索引變得無法使用；改變的是值的範圍，因此也改變規劃器預期要擷取的資料列數量。PostgreSQL：索引類型

假設有一張示意資料表，恰好包含 100,000 筆文章資料列，ID 是從 1 到 100,000 的連續整數：

條件式 符合的資料列 資料表占比
id < 11 10 0.01%
id > 11 99,989 99.989%
id = 11 1 0.001%

這些數量是根據合成輸入計算而來，並非對現有資料庫進行測量的結果。兩個範圍都不包含 ID 11。索引可以有效找出第一個範圍內符合條件的 10 個 ID。至於第二個範圍，使用索引反正也會找出幾乎所有資料列。

一般的 PostgreSQL 索引掃描會從索引取得項目，再從資料表的主要儲存區（稱為堆積區）擷取對應資料列。這些堆積資料列可能分散在許多頁面上。當幾乎每個頁面都需要讀取時，直接讀取資料表的成本可能低於遍歷索引並擷取索引所參照的資料列。PostgreSQL：僅索引掃描與涵蓋索引

因此，數字 11 對規劃器沒有特殊意義。換成不同資料時，大於條件也可能成為較窄的範圍。PostgreSQL 會利用資料表統計資料估算符合資料列的比例，這個比例稱為選擇性。單靠邊界值的大小，無法判定這個比例。PostgreSQL：規劃器使用的統計資料

如何在 Mac 上重現這項比較？

你需要現有的 PostgreSQL 連線，以及 Mac 的「終端機」中可用的 psql 指令。本練習不包含安裝步驟。使用 psql 開啟你平常使用的資料庫連線；它接受連線設定並提供互動式 SQL 提示字元。請在同一個全新工作階段中執行以下程式區塊。PostgreSQL：psql

設定步驟會建立只含合成文章文字的暫存資料表。工作階段結束時，暫存資料表就會消失。一般的整數主鍵會自動建立唯一 B-tree 索引，因此不需要在 id 上再建第二個索引。PostgreSQL：CREATE TABLE

SHOW server_version;

CREATE TEMP TABLE article_scan_demo (
    id integer PRIMARY KEY,
    title text NOT NULL,
    body text NOT NULL
);

INSERT INTO article_scan_demo (id, title, body)
SELECT
    g,
    'Article ' || g,
    repeat('Sample article text. ', 20)
FROM generate_series(1, 100000) AS s(g);

ANALYZE article_scan_demo;

SELECT
    count(*) AS total_rows,
    count(*) FILTER (WHERE id < 11) AS below_11,
    count(*) FILTER (WHERE id > 11) AS above_11
FROM article_scan_demo;

PostgreSQL 的整數 generate_series 在預設步長為一時會包含兩端端點。因此，計數查詢應該會回傳總計 100,000 筆資料列、小於 11 的 10 筆，以及大於 11 的 99,989 筆。PostgreSQL：傳回資料列的函式

載入資料列後執行 ANALYZE，讓規劃器取得已填入資料表的統計資料。這個指令會收集統計資料，作用不同於 EXPLAIN ANALYZE 中用來測量執行的選項。PostgreSQL：ANALYZE

接著，比較以下兩個完整資料列查詢：

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id < 11;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

這些指令會執行各個查詢並回報其計畫和實際資料列數量，但不會回傳所有選取的文章資料列。BUFFERS 會加入區塊活動資訊；TIMING OFF 則會停用個別節點的計時。這有助於研究查詢執行，但不會測量將所有符合條件的文章資料傳送至桌面應用程式所需的時間。PostgreSQL：EXPLAIN

在一般設定下，預期窄範圍會使用索引式計畫，而寬範圍會使用循序掃描。請將這些視為預期結果：PostgreSQL 可能根據估算選擇點陣圖計畫或其他適用計畫。本文檢視過這個範例的 SQL，但準備期間無法使用 PostgreSQL 伺服器執行；資料列數的算術則另行核對。

計畫中的哪些部分能說明這項選擇？

先查看掃描節點、預估的 rows 及實際的 rows。對第一個查詢，找出帶有 Index Cond 的 Index Scan，或 Bitmap Heap Scan 下方的 Bitmap Index Scan。對第二個查詢，找出帶有 Filter 的 Seq Scan。點陣圖計畫仍會使用索引；它會先整理符合條件的資料列位置，再擷取堆積頁面。PostgreSQL：使用 EXPLAIN

接著，檢查以下觀察結果：

本例中的觀察結果 要調查的事項
預估數量大致符合實際的 10 和 99,989 筆資料列 符合數量的差異足以解釋不同的選擇。
預估資料列數與實際數量差異很大 變更規劃器設定前，先重新整理統計資料。
在小得多的樣本中，兩個條件式都使用循序掃描 資料表大小可能使直接掃描更便宜。
出現點陣圖計畫 也將其視為索引式替代方案，比較其資料列估算和堆積區工作量。

規劃器成本是以任意單位表示的估算值，不是毫秒。請將成本視為 PostgreSQL 模型的一部分來比較，不要把 100 的成本解讀成時間長度。由於此範例使用暫存資料表，緩衝區活動會顯示在本機區塊項目下；一般資料表和索引的緩衝區則會顯示在共用區塊項目下。PostgreSQL：EXPLAIN

如果估算結果令人意外，請在同一個工作階段中執行以下診斷查詢：

SELECT
    schemaname,
    n_distinct,
    histogram_bounds,
    correlation
FROM pg_stats
WHERE schemaname = (
    SELECT nspname
    FROM pg_namespace
    WHERE oid = pg_my_temp_schema()
)
  AND tablename = 'article_scan_demo'
  AND attname = 'id';

直方圖描述用於範圍估算的值分布。相關性描述欄位的邏輯順序與資料列實體順序的接近程度；值接近任一極端時，索引存取堆積區看起來可能比較便宜。兩個欄位都無法界定 PostgreSQL 必須切換掃描方式的通用百分比。PostgreSQL：pg_stats

如何透過 node-postgres 比較查詢？

node-postgres 會將查詢文字和參數值傳送至 PostgreSQL，由 PostgreSQL 執行規劃。其查詢 API 接受 SQL，後面接著一個值陣列，因此兩次比較都可以使用相同的邊界值 11。node-postgres：查詢

若要獨立重現 Node.js 的結果，請準備可正常運作的 Node.js/npm 安裝環境，以及你現有的 PostgreSQL 連線設定。在新的專案目錄中執行 npm init -y，再執行 npm install pg。將下方完整程式儲存為 compare-scans.mjs，然後執行 node compare-scans.mjs。用戶端會使用 PostgreSQL 環境設定，例如 PGHOST、PGPORT 和 PGDATABASE；請使用現有本機資料庫的設定。node-postgres：連線

import pg from 'pg';

const client = new pg.Client();

try {
  await client.connect();

  await client.query(`
    CREATE TEMP TABLE article_scan_demo (
      id integer PRIMARY KEY,
      title text NOT NULL,
      body text NOT NULL
    )
  `);

  await client.query(`
    INSERT INTO article_scan_demo (id, title, body)
    SELECT
      g,
      'Article ' || g,
      repeat('Sample article text. ', 20)
    FROM generate_series(1, 100000) AS s(g)
  `);

  await client.query('ANALYZE article_scan_demo');

  const queries = [
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id < $1',
    'EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT * FROM article_scan_demo WHERE id > $1'
  ];

  for (const text of queries) {
    const result = await client.query(text, [11]);
    console.log(text);
    console.log(
      result.rows.map(row => row['QUERY PLAN']).join('\n')
    );
  }
} finally {
  await client.end();
}

程式會建立自己的範例資料表，並讓所有操作都使用同一個連線。它會列印兩個計畫，之後關閉連線。準備期間已使用 Node.js 檢查 JavaScript 語法，但未執行資料庫操作。如果連線設定失敗，請先解決問題，再解讀掃描行為。

具名預備陳述式會改變比較結果嗎？

在 node-postgres 查詢設定中加入 name，會在每個連線上建立具名預備陳述式。這會帶來另一項差異：PostgreSQL 可以根據參數值使用自訂計畫，也可以使用跨多次執行共用的通用計畫。node-postgres：查詢、PostgreSQL：PREPARE

在 PostgreSQL 18 的預設自動政策下，前五次執行會使用自訂計畫。接著 PostgreSQL 會比較通用計畫的預估成本與自訂計畫的平均成本；它不會在第六次執行時自動切換成通用計畫。PostgreSQL：PREPARE

如果應用程式中的具名陳述式行為與直接撰寫的 SQL 測試不同，請在同一個連線中使用 EXPLAIN EXECUTE 檢查該陳述式。通用計畫會顯示參數符號，自訂計畫則會代入提供的值。由於兩個比較運算子的 SQL 文字不同，請將它們保留為不同的具名陳述式。PostgreSQL：PREPARE

何時應該變更查詢或規劃器設定？

首先，保留查詢的實際需求。將 SELECT * 改成 SELECT id，會改變 PostgreSQL 必須擷取的資料。若適當的索引包含所有必要欄位，就能使用僅索引掃描。避免擷取堆積區資料列也取決於可見性對照表，因此僅索引計畫不保證完全不存取堆積區。PostgreSQL：僅索引掃描與涵蓋索引

如果工作需要所有符合條件的文章，請在比較計畫時保留這項需求。如果只需要一小部分排序後的資料，請改為測試實際的 ORDER BY 和 LIMIT 查詢：排序和提早終止可能會改變較佳計畫。PostgreSQL：使用 EXPLAIN

檢查統計資料後，你可以在現有的 SQL 工作階段中比較另一種計畫：

BEGIN;
SET LOCAL enable_seqscan = off;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF)
SELECT * FROM article_scan_demo WHERE id > 11;

ROLLBACK;

這項設定會降低循序掃描的優先度，但無法完全禁止循序掃描。請將結果用於診斷比較。PostgreSQL 也會透過 seq_page_cost 和 random_page_cost 建模循序及非循序頁面存取。調整兩者的相對值可能會改變計畫選擇，但文件建議根據查詢工作負載評估成本設定，而非只看少數實驗。PostgreSQL：查詢規劃

對這張合成資料表而言，關鍵差異是符合條件的 10 筆資料列與 99,989 筆資料列。請確認確切查詢、重新整理統計資料、比較預估和實際資料列數，並在相關時檢查預備計畫的行為。這些步驟能確認循序掃描是否符合所要求的工作，也能為任何進一步變更提供依據。

來源