PostgreSQL GIN Index 教學：JSONB 陣列反向查詢

本文以 「模型 ↔ 商品 ID 列表」反向查詢 為例，說明為什麼需要 GIN index、如何建立，以及應用層如何撰寫對應 SQL。

---

1. 問題背景：正向好查，反向難查

資料模型

假設每個商品模型（Model）可以預設一組商品 ID，存在 model_preset_config 表：

CREATE TABLE model_preset_config (
    preset_id   bigint PRIMARY KEY,
    preset_name varchar(255) NOT NULL,
    model_id    bigint NOT NULL,
    product_ids jsonb NOT NULL,  -- 建議使用 jsonb，後文說明原因
    created_at  timestamptz NOT NULL DEFAULT now(),
    updated_at  timestamptz NOT NULL DEFAULT now()
);

product_ids 是一個 JSON array，例如：

["101", "102", "103"]

兩種查詢方向

方向	問題	難度
正向	給定 model_id，列出它包含哪些 product	簡單，WHERE model_id = ?
反向	給定 product_id，找出哪些 model 用了它	困難，需掃描 JSON array 內容

反向查詢的常見需求：列出商品清單時，附帶「這個商品被哪些 model 使用」的關聯資訊。

若沒有索引，PostgreSQL 只能對每一列做 Seq Scan + JSON 解析，資料量一大就會很慢。

---

2. 為什麼選 GIN Index？

PostgreSQL 的 GIN（Generalized Inverted Index） 適合索引「複合值內的元素」，例如：

• JSONB 陣列裡的每個值
• 全文搜尋的 token
• array 型別的元素

本範例用的是 JSONB containment 運算子 @>：

-- 「這個 preset 的 product_ids 是否包含 101？」
p.product_ids @> '["101"]'::jsonb   -- true / false

GIN index 能讓 @> 走 Bitmap Index Scan，而不是全表掃描。

注意：這裡的 GIN 是 PostgreSQL 的 GIN index，不是 Go 的 Gin web framework。

---

3. Migration：建立 GIN Index

以下示範完整的 migration 步驟：

-- 若欄位原本是 json 型別，需先轉為 jsonb（GIN 索引只支援 jsonb）
ALTER TABLE model_preset_config
    ALTER COLUMN product_ids TYPE jsonb USING product_ids::jsonb;

-- 確保每個 model 只有一筆 product preset（避免重複 row 造成查詢結果重複）
CREATE UNIQUE INDEX IF NOT EXISTS idx_preset_product_one_per_model
    ON model_preset_config (model_id)
    WHERE preset_name = 'product_preset';

-- GIN index：product_id → model 反向查詢（@> containment）
CREATE INDEX IF NOT EXISTS idx_preset_product_ids_gin
    ON model_preset_config USING GIN (product_ids)
    WHERE preset_name = 'product_preset';

三步驟拆解

Step 1：JSON → JSONB

GIN index 需要 jsonb 型別（json 不支援 GIN 索引）。Migration 用 USING product_ids::jsonb 做型別轉換。

Step 2：Partial Unique Index

CREATE UNIQUE INDEX ... (model_id) WHERE preset_name = 'product_preset';

確保每個 model 只有一筆 product_preset，避免重複 row 造成查詢結果重複。

Step 3：Partial GIN Index

CREATE INDEX ... USING GIN (product_ids) WHERE preset_name = 'product_preset';

• USING GIN：指定 GIN 索引類型
• WHERE preset_name = 'product_preset'：Partial index，只索引 product preset 的 row，縮小索引體積、加快維護

---

4. 寫入路徑：如何維護 product_ids

應用層在更新 model 的商品列表時，通常會序列化 ID 陣列後寫入：

// 將 ID 以字串存入 JSON，避免大整數在 JSON number 中精度損失
ids := make([]string, 0, len(products))
for _, p := range products {
    if p != nil && p.ID > 0 {
        ids = append(ids, strconv.FormatInt(p.ID, 10))
    }
}
productJSON, err := json.Marshal(ids)
// ...
existingPreset.ProductIDs = productJSON

重點：

1. ID 以 字串 存入 JSON（["101","102"]），避免 JavaScript / JSON number 精度問題
2. 若已有 preset → UPDATE；否則 INSERT
3. GIN index 在 UPDATE/INSERT 時 自動維護，應用層不需額外操作

---

5. 讀取路徑：兩種 SQL 查詢模式

模式 A：批量反向查詢（主要用法）

給定多個 product_id，一次查出所有 model 關聯：

SELECT aid AS product_id,
       m.model_id,
       m.model_name,
       c.category_id,
       c.category_name
  FROM model_preset_config p
  JOIN models m ON m.model_id = p.model_id
  JOIN categories c ON c.category_id = m.category_id
 CROSS JOIN unnest($1::bigint[]) AS aid
 WHERE p.preset_name = 'product_preset'
   AND (
       p.product_ids @> jsonb_build_array(aid::text)
    OR p.product_ids @> jsonb_build_array(aid)
   )
 ORDER BY aid, m.model_id;

SQL 技巧說明

語法	用途
CROSS JOIN unnest($1::bigint[])	把參數陣列展開成多列，一次查多個 product
@> jsonb_build_array(aid::text)	字串格式 ["101"] 的 containment 檢查
@> jsonb_build_array(aid)	數字格式 [101] 的 containment 檢查（legacy 資料）
WHERE preset_name = 'product_preset'	對應 partial index 條件，讓 planner 選 GIN index

為什麼要 OR 兩種格式？

歷史資料可能是 numeric array [10, 20]，新資料是 string array ["10","20"]。兩種 @> 確保都能命中 GIN index：

-- 新格式
'["101","102"]'::jsonb @> '["101"]'::jsonb   -- ✅

-- 舊格式
'[101,102]'::jsonb @> '[101]'::jsonb          -- ✅
'[101,102]'::jsonb @> '["101"]'::jsonb        -- ❌ 型別不同，比對失敗

模式 B：EXISTS 子查詢（篩選用法）

在列出 products 時，若需依 model_id 過濾「屬於某 model 的商品」，可用 EXISTS：

SELECT *
  FROM products
 WHERE 1=1
   AND EXISTS (
       SELECT 1
         FROM model_preset_config p
        WHERE p.model_id = $1
          AND p.preset_name = 'product_preset'
          AND (
              p.product_ids @> jsonb_build_array(product_id::text)
           OR p.product_ids @> jsonb_build_array(product_id)
          )
   );

這裡 @> 的左邊是 外層 products 表的 product_id，右邊用 jsonb_build_array 建單元素 array 做 containment 比對。

---

6. 端到端資料流

Service 層可並行 batch 查詢關聯資料，避免 N+1：

func (s *Service) fetchModelUsages(ctx context.Context, productIDs []int64) {
    go func() {
        usages, err := s.repo.ListModelUsagesByProductIDs(ctx, productIDs)
        // merge into response...
    }()
}

---

7. 驗證 Index 是否生效

在 PostgreSQL 中手動驗證：

-- 查看 index 是否存在
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'model_preset_config';

-- 用 EXPLAIN ANALYZE 確認走 GIN index
EXPLAIN (ANALYZE, BUFFERS)
SELECT m.model_id, m.model_name
  FROM model_preset_config p
  JOIN models m ON m.model_id = p.model_id
 WHERE p.preset_name = 'product_preset'
   AND p.product_ids @> '["101"]'::jsonb;

預期看到類似：

Bitmap Index Scan on idx_preset_product_ids_gin
  Index Cond: (product_ids @> '["101"]'::jsonb)
  Filter: (preset_name = 'product_preset'::text)

若出現 Seq Scan on model_preset_config，代表 index 沒被用到，需檢查：

1. product_ids 是否已轉為 jsonb
2. @> 右側格式是否與資料一致
3. WHERE preset_name = 'product_preset' 是否與 partial index 條件一致
4. 表統計是否過舊（ANALYZE model_preset_config）

---

8. 設計決策摘要

決策	原因
GIN 而非 B-tree	B-tree 無法索引 JSON array 內的元素
Partial index	只索引特定 preset 類型，縮小 index、加快寫入
JSONB 而非 JSON	只有 JSONB 支援 GIN index 和 @>
字串 ID 格式	避免大整數 ID 在 JSON number 中精度損失
雙格式 OR	相容 legacy numeric array 資料
unnest batch 查詢	一次查多個 product，避免 N+1
不用 reverse lookup table	GIN on JSONB 已足夠，減少資料同步成本

---

9. 新增類似 GIN 查詢的 Checklist

1. 確認欄位是 JSONB（不是 JSON）
2. Migration 建立 USING GIN (column)，必要時加 partial WHERE
3. 查詢使用 @> containment，右側用 jsonb_build_array(...) 建單元素 array
4. 應用層用參數化 SQL，避免字串拼接
5. 用 EXPLAIN ANALYZE 確認 planner 選 GIN index
6. 寫入路徑確保 index 能自動更新（一般 INSERT/UPDATE 即可）

---

10. Python 對照範例（SQLAlchemy）

若使用 Python + SQLAlchemy，批量反向查詢可寫成：

from sqlalchemy import text
from sqlalchemy.ext.asyncio import AsyncSession

async def list_model_usages_by_product_ids(
    session: AsyncSession,
    product_ids: list[int],
) -> list[dict]:
    query = text("""
        SELECT aid AS product_id,
               m.model_id,
               m.model_name
          FROM model_preset_config p
          JOIN models m ON m.model_id = p.model_id
         CROSS JOIN unnest(:product_ids) AS aid
         WHERE p.preset_name = 'product_preset'
           AND (
               p.product_ids @> jsonb_build_array(aid::text)
            OR p.product_ids @> jsonb_build_array(aid)
           )
         ORDER BY aid, m.model_id
    """)
    result = await session.execute(query, {"product_ids": product_ids})
    return [dict(row._mapping) for row in result]

PostgreSQL 的 unnest 搭配 SQLAlchemy 的 bound parameter 時，需確認 driver 支援 array 型別傳遞（例如 asyncpg 的 product_ids 可直接傳 list[int]）。