PostgreSQL 中 `SELECT ... WHERE id IN (大量值)` 效能實測與最佳實務

電商系統常見需求：依數百至數千個分散 ID 批次查詢商品屬性。網路上建議五花八門（臨時表 JOIN、分批查詢、UNION ALL 等），本文在 PostgreSQL 9.4 實測環境 (50 萬筆測試資料、唯一 B-tree 索引) 驗證各方案真實效能。

測試環境建立

CREATE TABLE t9 (
    c1 INTEGER PRIMARY KEY,
    c2 CHAR(100),
    c3 CHAR(200)
);
CREATE UNIQUE INDEX i1 ON t9 (c1);

-- 產生 50 萬筆測試資料
INSERT INTO t9 
SELECT generate_series(1000, 500000, 1), repeat('a', 90), repeat('b', 180);

方案一：直接 `IN` 清單

產生 100 / 1,000 / 10,000 個隨機 ID 組成 `IN` 清單：

方案二：建立臨時表再 JOIN

CREATE TEMP TABLE t_tmp (id INT);
INSERT INTO t_tmp VALUES (...);  -- 同樣 100/1,000/10,000 筆
SELECT t9.* FROM t9 JOIN t_tmp ON t9.c1 = t_tmp.id;

結論與最佳實務

1. 1,000 筆以內： 直接 `IN` 清單最簡潔、效能最佳，無需額外物件。
2. 10,000 筆以上： `IN` 清單雖較臨時表 JOIN 快 (356 ms vs 550 ms)，但 SQL 字串過長可能觸發解析器/網路封包限制，建議改用 VALUES 子句 或 UNNEST 陣列參數 (PostgreSQL 特有)：

-- VALUES 寫法 (標準 SQL)
SELECT t9.* FROM t9 
JOIN (VALUES (494613), (575087), ...) AS v(id) ON t9.c1 = v.id;

-- UNNEST 陣列參數 (需驅動程式支援)
SELECT * FROM t9 WHERE c1 = ANY($1);  -- $1 為 integer[] 參數

3. 臨時表方案額外負擔：DDL 鎖定、寫入 WAL、統計資訊缺失導致 Merge Join 退化、連線池污染。
4. MySQL 行為類似：`IN` 清單在千筆內極快，萬筆以上建議 `JOIN` 衍生表或 `WHERE id IN (SELECT id FROM temp_table)`。

核心心法： 信任資料庫優化器，優先用最直觀的 `IN` 清單；只有當參數量級確實超過優化器甜蜜點 (通常數千~萬筆) 時，才引入陣列參數或臨時表，並務必實測驗證。