PostgreSQL JSONB GIN 索引可利用可擷取的路徑對常數相等測試，縮小 JSONPath 搜尋範圍。範圍比較、不等比較和單獨的路徑存在測試本身不會提供這些搜尋鍵。混合謂詞則需要進一步檢查。

本指南將協助使用 Mac 終端機的開發者，為篩選合成文章資料列選擇索引、透過 node-postgres 傳送查詢，並檢視產生的執行計畫。核心擷取規則記載於 PostgreSQL 的 JSONB 索引參考文件。

哪些謂詞形式能提供實用的 GIN 搜尋鍵？

對於 @? 和 @@，PostgreSQL 會擷取將存取器鏈結與常數相連的相等子句。支援的存取器包括具名成員、陣列萬用字元和陣列索引。兩種 JSONB GIN 運算子類別都支援這些形式；jsonb_ops 另外支援物件萬用字元和遞迴下降。JSON 類型。

JSONPath 表達式 能否擷取實用的搜尋條件？
$.category == "layout" 可以，兩種運算子類別皆支援
$.tags[*] == "grid" 可以，兩種運算子類別皆支援
$.tags[0] == "grid" 可以，兩種運算子類別皆支援；仍須檢查完整謂詞
$.* == "layout" jsonb_ops 支援，jsonb_path_ops 不支援
$.**.category == "layout" jsonb_ops 支援，jsonb_path_ops 不支援

以下界線是根據 PostgreSQL 18 的擷取實作而得。此處的「無」表示單獨使用該表達式時，不會產生實用的擷取條件；這不代表執行計畫會使用特定的掃描節點名稱。PostgreSQL 18 JSONB GIN 實作。

JSONPath 表達式 擷取的條件
$.words > 1200 無
$.category != "layout" 無
exists($.category) 無
$.title like_regex \"Grid\" 無
$.words == $.target_words 無：兩個運算元都不是純量常數

為什麼 AND 和 OR 的行為不同？

使用 $.category == "layout" && $.words > 1200 時，相等條件可以篩選候選資料列；完整條件會再檢查一次。使用 $.category == "layout" || $.words > 1200 時，若只採用相等條件，會漏掉符合範圍條件的資料列。因此，只要其中一個分支不受支援，該 OR 的擷取就會失敗。由 OR 連接兩個受支援相等條件，則能提供搜尋條件。這些規則只適用於單一 JSONPath 表達式內的擷取；其他 SQL 條件仍可能影響整體執行計畫。PostgreSQL 18 JSONB GIN 實作。

應該選擇哪種運算子和運算子類別？

使用 @? 來判斷路徑是否傳回任何項目，通常會透過篩選器進行。使用 @@ 來檢查布林謂詞。將單獨的布林比較傳給 @? 是個陷阱：即使布林值為 false，它仍是傳回的項目。PostgreSQL 在 JSON 函式與運算子 中明確區分這些形式。

以下兩個查詢針對下文介紹的範例文章結構，表達相同的工作：

SELECT id
FROM jsonpath_article_demo
WHERE doc @? '$.tags[*] ? (@ == "grid")';

SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.tags[*] == "grid"';

對於示範中的具名路徑相等查詢，請選擇 jsonb_path_ops。如果還需要 ? 等 SQL 鍵存在運算子，或上述萬用字元存取器，請選擇預設的 jsonb_ops。兩者都支援 @>、@? 和 @@；支援某個運算子本身並不代表每個謂詞都能有效縮小搜尋範圍。JSONB 索引。

範例以 PostgreSQL 18 為目標，這是目前擷取到的官方文件所標示的版本。SQL/JSON 路徑支援始於 PostgreSQL 12，因此較早的伺服器無法使用這些運算子。PostgreSQL 12 現已列為不受支援；其版本說明指出功能引入的界線，並非建議安裝的版本。PostgreSQL 12 版本說明。

如何在 Mac 上重現查詢？

準備一個正在執行的 PostgreSQL 18 資料庫、一個可在終端機使用的 psql 指令，以及透過一般連線設定選取的現有暫存資料庫。本練習會建立新資料表，不會取代現有資料表。如果該資料表名稱已存在，請改用另一個暫存資料庫，或在整個示範中一致地更改資料表名稱。

在終端機開啟 psql，執行 SHOW server_version;，然後貼上以下完整的設定內容。這會建立 20,000 筆合成文章資料列：每一百篇文章中有一篇的類別為 layout，字數則在 400 到 1,999 之間循環。

CREATE TABLE jsonpath_article_demo (
    id integer PRIMARY KEY,
    doc jsonb NOT NULL
);

INSERT INTO jsonpath_article_demo (id, doc)
SELECT
    n,
    jsonb_build_object(
        'title', 'Article ' || n,
        'category', CASE
            WHEN n % 100 = 0 THEN 'layout'
            ELSE 'notes'
        END,
        'words', 400 + n % 1600,
        'tags', jsonb_build_array(
            CASE WHEN n % 100 = 0 THEN 'grid' ELSE 'text' END
        )
    )
FROM generate_series(1, 20000) AS sample(n);

CREATE INDEX jsonpath_article_demo_doc_gin
ON jsonpath_article_demo USING gin (doc jsonb_path_ops);

ANALYZE jsonpath_article_demo;

SELECT id, doc->>'title' AS title
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200'
ORDER BY id
LIMIT 5;

推算出的前五個 ID 是 900、1000、1100、1200 和 1300；完整條件會符合 84 筆產生的資料列。這些是示範資料集的結果，不是效能測量值。算術結果和預期 ID 已用一段小型 JavaScript 計算確認；研究環境無法執行 PostgreSQL。

ANALYZE 會收集規劃器選擇執行計畫時使用的資料表統計資訊。在載入資料後執行此指令，能為本示範提供較實用的規劃基準。ANALYZE。

如何判斷 GIN 是否有幫助？

完成設定後，在同一個資料庫執行以下兩個完整的診斷查詢。第一個包含可擷取的相等條件；第二個則單獨測試範圍謂詞。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.category == "layout" && $.words > 1200';

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE doc @@ '$.words > 1200';

對第一個查詢，請尋找名為 jsonpath_article_demo_doc_gin 的 Bitmap Index Scan，它通常會位於 Bitmap Heap Scan 之下。請一併查看 Index Cond、Recheck Cond、實際資料列數、緩衝區活動和執行時間。索引條件中顯示完整 JSONPath，不代表每個比較都成為索引搜尋鍵。PostgreSQL 的使用 EXPLAIN 說明了這些執行計畫欄位，以及擷取候選資料列與後續篩選之間的差別。

僅憑順序掃描，不能證明查詢不符合索引使用條件。規劃器會根據估計成本來選擇執行計畫；資料表較小或符合條件的資料比例較高時，掃描可能更有效率。反過來說，光是看到索引節點，也無法證明索引有效縮小了範圍。在條件相近的情況下，比較執行的工作量和經過時間。EXPLAIN ANALYZE 會實際執行每個查詢。使用 EXPLAIN。

在本練習中，實際要判斷的是：類別相等條件是否能充分減少候選資料列，讓評估字數條件的成本降低。產生的類別分布提供了明確的測試案例，但無法據此判定分布不同的文章集合應採用何種最佳執行計畫。

node-postgres 應如何傳入 JSONPath？

將完整 JSONPath 作為一個 SQL 參數傳入，並將其轉型為 jsonpath。SQL 佔位符 $1 和 JSONPath 根路徑 $ 屬於不同語言。node-postgres 接受查詢文字和一個值陣列；伺服器會將提供的路徑作為參數值接收。查詢。

使用目前受支援的 Node.js 安裝版本，在工作目錄執行 npm install pg。將以下完整程式儲存為 article-query.mjs。它需要先在同一個資料庫中完成上述 SQL 設定。套件安裝指令來自 node-postgres 入門指南。

import pg from 'pg';

const { Client } = pg;
const client = new Client();
const category = 'layout';
const path = `$.category == ${JSON.stringify(category)} && $.words > 1200`;

try {
  await client.connect();

  const result = await client.query({
    text: `
      SELECT id, doc->>'title' AS title
      FROM jsonpath_article_demo
      WHERE doc @@ $1::jsonpath
      ORDER BY id
      LIMIT 5
    `,
    values: [path],
  });

  console.table(result.rows);
} catch (error) {
  console.error(error.message);
  process.exitCode = 1;
} finally {
  await client.end();
}

設定好暫存資料庫的資料庫連線環境變數後，執行 node article-query.mjs。Client 會讀取 PostgreSQL 連線變數，例如 PGHOST、PGPORT、PGDATABASE 和 PGUSER。程式會回報連線失敗或找不到示範資料表的情況。連線。

此處的 JSON.stringify(category) 會建立 JSONPath 字串常值。產生的路徑仍包含對常值 \"layout\" 的相等比較；透過 SQL 參數傳遞路徑，不會將此相等比較改成 JSONPath 變數。

如果改用 jsonb_path_exists 及其 vars 引數，你呼叫的是使用具名 JSONPath 變數的函式。這是文件記載的評估介面，但不是可索引的 @? 運算子形式。若要進行這項 GIN 查詢，請使用上述運算子查詢。JSON 函式與運算子、JSONB 索引。

主要條件是數值範圍時該怎麼做？

如果主要工作是依字數選取文章，請透過 B-tree 運算式索引將該純量欄位直接提供給索引使用。PostgreSQL 說明 B-tree 支援相等比較和有序範圍比較，而運算式索引可為計算結果建立索引。索引類型、運算式索引。

在產生的資料表上執行以下額外設定和查詢：

CREATE INDEX jsonpath_article_demo_words_btree
ON jsonpath_article_demo (((doc->>'words')::integer));

ANALYZE jsonpath_article_demo;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id
FROM jsonpath_article_demo
WHERE (doc->>'words')::integer > 1200;

此查詢刻意使用與索引相同的運算式。其前提是每篇文章都有一個可轉換為整數的 words 值。產生的資料列符合此條件；任意文字或超出範圍的值可能會在建立索引或後續寫入時導致轉型失敗。若要處理陣列、混合類型或多筆相符值中的 JSONPath 比較，這種改寫並非通用替代方案。

可用這項界線來輔助判斷：由文件相等條件主導且具有選擇性的查詢，先考慮 GIN；若查詢主要依據類型一致的純量欄位進行範圍篩選，則可考慮 B-tree 運算式索引。先確保結果集符合預期，再根據實際執行計畫判斷索引帶來的效益是否足以抵銷維護成本。

來源