Luke Kong

Luke Kong

專精 Agentic AI Workflow、Multi-Agent（LangChain / LangGraph）、流程自動化（n8n、FastAPI）與 RAG / Text-to-SQL。持有 Azure AI Engineer 與 AWS 雲端架構師認證，專注將商業邏輯轉為可上線的 AI 與自動化系統，並以 LLM-as-a-judge 與 trace 分析確保 production 穩定度。

Posts by Luke Kong

PostgreSQL 效能優化（上）：LIKE 和 Regex，哪個比較快？

PostgreSQL 效能優化（上）：LIKE 和 Regex，哪個比較快？

這篇文章是我在工作上真的卡住過的一次效能問題。中間繞了一些路，最後才把真正的瓶頸抓出來。 本篇為上集，先談 LIKE 與 Regex 的抉擇與 I/O／CPU 迷思；接續兩集分別深入：中集：用 EXPLAIN 看見 Seq Scan 的真相 下集：用 Array + GIN 把標籤變成索引上集：當 LIKE 遇上 Regex，到底差在哪？ 1. 問題背景（白話版） 我在工作上接了一批 ETL 後的資料。每筆資料裡，很多標籤被塞在同一個欄位，用 ; 串起來，像這樣： tag1;tag2;tag3;tag_VIP;tag_inactive 當我要找特定標籤時，最直覺是寫很多個 LIKE。 2. 同事一句提醒，直接點到問題 同事說：「把多個 LIKE 合成一個 Regex，通常會更快。」 我第一反應是：「沒有索引的話，不是都要整張表一筆一筆看過去嗎？那真的會快多少？」 3. 我們都沒錯，只是看的是不同成本 後來我才搞懂，我們其實切入點不同：我先看讀取成本(I/O)，同事先看運算成本(CPU)。我看的是 I/O（讀資料成本）沒有索引時，資料庫通常還是要掃很多資料。這件事不會因為你改成 Regex 就消失。 同事看的是 CPU（運算成本）多個 LIKE 代表同一列要重複比對很多次；合成一個 Regex，通常可把「重複比對」變少。用比喻來說：多個 LIKE 像是同一本書翻很多輪，每次找一個關鍵字。 單一 Regex 像是一輪閱讀就把多個關鍵字一起判斷。4. 這篇上集我最想說的事 如果資料量還小，Regex 可能已經很有感。但資料一大，只換語法通常不夠，最後還是得讓資料庫用不同方式找資料。 結論Regex 可以先減輕 CPU 壓力，但只要查詢還在掃整張表，I/O 依然會是主瓶頸。中集從 EXPLAIN ANALYZE 拆解 LIKE 與 Regex 的真實成本，下集則動手實作 Array + GIN：PostgreSQL 效能優化（中）：用 EXPLAIN 看見 Seq Scan 的真相 PostgreSQL 效能優化（下）：用 Array + GIN 把標籤變成索引

Read More
PostgreSQL 效能優化（中）：用 EXPLAIN 看見 Seq Scan 的真相

PostgreSQL 效能優化（中）：用 EXPLAIN 看見 Seq Scan 的真相

若你還沒讀過 LIKE／Regex 與 I/O、CPU 兩條戰線，請先看上集；想直接動手做的，可以跳到下集：上集：當 LIKE 遇上 Regex 的觀念碰撞 下集：用 Array + GIN 把標籤變成索引問題升級：Regex 幫了 CPU，但整體還是不夠快 上集的結論提到 Regex 能降低每列字串比對的 CPU 成本，但當資料規模上看千萬、億級時，真正決定延遲與吞吐的是 I/O 與存取路徑，而不是字串語法本身。 這一集要做的就一件事：用 EXPLAIN ANALYZE 把計畫攤開，定位 LIKE / Regex 寫法在哪一步把整張表的 I/O 給綁死了。 確認瓶頸位置之後，下集再動手換存取路徑。關鍵分析：用 EXPLAIN 與複雜度看主導成本註：以下 EXPLAIN 與 SQL 已將 schema、表名、欄位名匿名化處理（例：customers、tag_dictionary、balance 等）。執行統計（rows、loops、Rows Removed、Buffers）來自一份測試資料集的 EXPLAIN ANALYZE 實測，僅作為「結構辨識」範例——關注每個節點長什麼樣、條件擺在哪、有沒有走索引；絕對數字會隨資料量變化，不代表生產環境。用 EXPLAIN ANALYZE 攤開 LIKE 版的計畫，第一個刺眼的訊息是 這條查詢對主表 Seq Scan 了三次。要弄懂為什麼會這樣，必須回到 SQL 語法本身。 從 SQL 找出每一個 Seq Scan 的源頭 把 LIKE 版本的 SQL 簡化、標出每個會碰到主表的位置： WITH product_dict AS ( -- ← 觸發點 1 SELECT DISTINCT product_id, product FROM customers WHERE product <> '' ) SELECT ... FROM customers AS main -- ← 觸發點 2 INNER JOIN product_dict ... WHERE main.segment = 'core' AND lower(main.levels) LIKE '%lv1%' AND lower(main.excluded_levels) NOT LIKE '%ex001%' AND lower(main.excluded_levels) NOT LIKE '%ex022%' AND date_trunc('MONTH', main.etl_date) = date_trunc('MONTH', (SELECT MAX(etl_date) FROM customers)); -- ← 觸發點 3三個觸發點在 SQL 上看起來很無辜：一個 CTE、一個主 FROM、一個 MAX() 子查詢。但對 PostgreSQL 來說，每一個都是「再把表打開讀一次」的指令。對應到 EXPLAIN： -> Aggregate -- 觸發點 3：InitPlan 1 -> Seq Scan on customers Buffers: shared hit=159-> Seq Scan on customers main -- 觸發點 2：主 FROM Filter: segment = 'core' AND lower(levels) ~~ '%lv1%' AND lower(excluded_levels) !~~ '%ex001%' AND lower(excluded_levels) !~~ '%ex022%' AND date_trunc('MONTH', etl_date) = (InitPlan 1).col1 Rows Removed by Filter: 3806 Buffers: shared hit=318-> Seq Scan on customers -- 觸發點 1：CTE 內部 Filter: product <> '' Rows Removed by Filter: 3835 Buffers: shared hit=159四個關鍵訊號：Seq Scan 出現三次：每一次都代表「主表逐列翻一遍」。 每個 Filter 之後的 Rows Removed 幾乎等於該節點吐出的 rows：被讀進來的列絕大多數只是為了被丟掉。 沒有條件被歸到 Index Cond：所有 WHERE 條件都落在 Filter 內，PostgreSQL 找不到任何走索引的入口。 三段 Buffers: shared hit 加總接近主表的全部 page 數：實質上整張表被完整讀了三次。為什麼 LIKE 把每一個掃描都鎖死在 Seq Scan PostgreSQL 預設只會挑 B-tree 索引來規劃查詢（除非你明確替欄位建了 GIN、GiST 等其他類型）。B-tree 是有序樹，能加速的查詢條件只有一種共通特徵：能在樹中定位出起始 key——例如 name = 'Luke' 或 name LIKE 'Lu%'，B-tree 都能從特定節點往下走。一旦條件失去這個性質，索引就無從切入。回到三個觸發點的條件：lower(levels) LIKE '%lv1%'：前後都是 %，B-tree 沒入口。 lower(excluded_levels) NOT LIKE '%ex001%'：NOT LIKE 等於要求對每一列驗證「條件不成立」，索引天生不擅長否定查詢。 date_trunc('MONTH', etl_date) = ...：欄位被函數包住，B-tree 找不到對應的 key（除非建 expression index）。所以三個觸發點全部退回 Seq Scan 的根因只有一個：LIKE 加上「函數包欄位」的寫法根本沒給 PostgreSQL 任何走索引的入口。 改寫成 Regex 之後，SQL 跟 EXPLAIN 變這樣 把兩條 NOT LIKE 改用 Regex（從字典表動態組出 alternation pattern）後，SQL 結構整個換掉： WITH remove_list AS ( -- ← 觸發點 1 SELECT '(^|;|,)(' || REPLACE(string_agg(TRIM(code), '|'), ' ', '') || ')($|;|,)' AS regex_pattern FROM tag_dictionary WHERE kind = 'exclude' AND category IN ('low_marketing', 'private_bank') ) SELECT cluster_list.display_name, main.etl_date, product_list.product, SUM(main.balance)::numeric / COUNT(DISTINCT main.customer_id) AS avg_balance FROM ( SELECT * FROM customers -- ← 觸發點 2 WHERE customer_id <> '' AND segment = 'core' AND etl_date = (SELECT MAX(etl_date) FROM customers) -- ← 觸發點 3 ) AS main LEFT JOIN ( SELECT DISTINCT '%' || lower(code) || '%' AS level_code, display_name FROM tag_dictionary -- ← 觸發點 4 WHERE display_name = 'HVIP' ) AS cluster_list ON main.levels LIKE cluster_list.level_code LEFT JOIN ( SELECT DISTINCT product_id, product FROM customers -- ← 觸發點 5 WHERE customer_id = '' AND product <> '' ) AS product_list ON main.product_id = product_list.product_id CROSS JOIN remove_list WHERE COALESCE(main.excluded_levels, '') !~ remove_list.regex_pattern AND cluster_list.display_name <> '' AND main.product_id <> 0;五個觸發點對應 EXPLAIN 的五個 Seq Scan： -> Aggregate -- 觸發點 3：InitPlan 1（同 LIKE 版） -> Seq Scan on customers Buffers: shared hit=159-> Aggregate -- 觸發點 1：remove_list CTE -> Seq Scan on tag_dictionary Filter: kind = 'exclude' AND category IN ('low_marketing','private_bank') Buffers: shared hit=4-> Seq Scan on tag_dictionary -- 觸發點 4：cluster_list 子查詢 Filter: display_name = 'HVIP' Buffers: shared hit=4-> Seq Scan on customers main -- 觸發點 2：主 FROM Filter: customer_id <> '' AND product_id <> 0 AND segment = 'core' AND etl_date = (InitPlan 1).col1 Rows Removed by Filter: 3308 Buffers: shared hit=318-> Seq Scan on customers -- 觸發點 5：product_list 子查詢 Filter: product <> '' AND customer_id = '' Rows Removed by Filter: 3835 Buffers: shared hit=159主表 customers 還是被 Seq Scan 三次（觸發點 2、3、5），完全沒少；多出來的兩次 Seq Scan 在字典表上（觸發點 1、4）。 更關鍵的是，原本擠在主 Filter: 裡的 LIKE 與 Regex，現在被拆到兩層 Join Filter:： -> Nested Loop -- 套 Regex Join Filter: COALESCE(excluded_levels, '') !~ regex_pattern-> Nested Loop -- 套 LIKE on levels Join Filter: levels ~~ ('%' || lower(code) || '%')LIKE 版 vs Regex 版的結構性對照項目 LIKE 版 Regex 版主表 Seq Scan 次數 3 3字典表 Seq Scan 次數 0 2LIKE / Regex 條件擺在哪 主表的 Filter:（隨 Seq Scan 同時評估） Join Filter:（join 階段才評估）主表 Seq Scan 直接吐出的列 已經套完 LIKE 條件 只套基本條件，字串檢查留待 join 處理兩個結構性結論：主表的 Seq Scan 次數沒變：3 次仍然 3 次。Regex 沒有解決「掃幾次」這件事。 LIKE / Regex 從 Filter 升到 Join Filter：字串檢查被推到 join 階段，主表必須先把所有通過基本條件的列都吐進 join，才開始用 LIKE / Regex 篩。Regex 在 per-row CPU 上理論上更輕（下面解釋為什麼），這個結構性改寫等於把省下的力氣再吐回去。也就是說：SQL 結構決定要掃幾次表，LIKE 或 Regex 只決定每次掃描如何檢查每一列。 為什麼理論上 Regex 能把 CPU 從 $O(N \times M)$ 壓成 $O(N)$ 要理解這個差別，得先補一個概念：狀態機（finite automaton）就是一台「邊讀字元、邊跳狀態」的小機器——給它一個字串，它依序看每一個字元，決定要跳到哪個狀態，最後告訴你「有沒有匹配成功」。Regex 引擎在執行前會把整個 pattern 翻譯成這種結構，常見有兩個變體：DFA（Deterministic Finite Automaton，確定性自動機）：每個狀態看到一個字元只有一條轉移路徑，執行快但狀態總數可能爆炸。 NFA（Nondeterministic Finite Automaton，非確定性自動機）：每個狀態同一個字元可以有多條轉移路徑，狀態少但執行可能要 backtrack。PostgreSQL 用的是 NFA-based 引擎。對 alternation (ex001|ex022|ex045)，編譯出來的狀態機長這樣（簡化）： ┌─ 0 → 0 → 1 ✓ ex001 start ─→ e ─→ x ─→ 0 ──────┼─ 2 → 2 ✓ ex022 └─ 4 → 5 ✓ ex045關鍵是共用前綴 ex0 只被處理一次。讀進輸入字串後，每讀一個字元，整台狀態機同時推進所有分支，掃過字串一輪就等於一次判斷完所有替代項。 把這套機制套回查詢：多 LIKE：M 個 pattern 各自獨立、不共用工作，每列要跑 M 次完整字串比對 → CPU ~ $O(N \times M)$ 單 Regex：M 個替代項合併成同一台狀態機，每列只走一次 → CPU ~ $O(N)$省的是「每列的 CPU」，沒有改變「要不要再掃一次表」這件事。 三種寫法的成本主導者 定義符號：N = 主表列數、M = 條件數、K = 命中列數。寫法 每次掃描的 CPU 每次掃描的 I/O多 LIKE ~ $O(N \times M)$ ~ $O(N)$ 全表掃單 Regex ~ $O(N)$ ~ $O(N)$ 全表掃Array + GIN ~ $O(\log N + K)$ 走索引 + 抓命中頁LIKE 與 Regex 只動 CPU 那一欄，I/O 那欄一動沒動；而且 SQL 結構讓查詢碰主表幾次，整體成本就是上表那一欄按次累計。要根本改變每一次掃描的形狀，得讓 Seq Scan 退場、換 Index Scan 上場。 說白了，真正拉開差距的是有沒有讓查詢改走索引那條路。中集小結 到這裡我們已經看清三件事：SQL 結構決定要碰主表幾次（CTE、子查詢、MAX() 各算一次觸發點） LIKE 加上「函數包欄位」讓每一次掃描都鎖死成 Seq Scan：B-tree 只能加速能定位起始 key 的條件，本案三個觸發點全部不符合 Regex 改寫不會減少掃描次數：狀態機只壓 per-row CPU，不改變存取路徑要根本拉開差距，得直接換索引型存取路徑——下集動手做：PostgreSQL 效能優化（下）：用 Array + GIN 把標籤變成索引

Read More
PostgreSQL 效能優化（下）：用 Array + GIN 把標籤變成索引

PostgreSQL 效能優化（下）：用 Array + GIN 把標籤變成索引

若你還沒讀過前兩集的問題定位與 EXPLAIN 拆解：上集：當 LIKE 遇上 Regex 的觀念碰撞 中集：用 EXPLAIN 看見 Seq Scan 的真相從觀察到動手：真正要換掉的是存取路徑 中集確認的事實是：LIKE 與 Regex 都讓 PostgreSQL 規劃出全表 Seq Scan，因為 B-tree 索引在 '%xxx%' 條件下找不到入口。要根本改變查詢成本，得做兩件事：改資料模型讓查詢語意能對應到合適的索引型態，建索引讓 PostgreSQL 有索引可走。實作方案：把標籤查詢改成索引友善路徑 我把調校拆成兩步，簡單直接：先改資料型態，再改查詢寫法讓它吃到索引。 A. 資料模型調整（字串 -> 陣列） 先把 'tag1;tag2' 轉為 PostgreSQL 原生 text[]，讓查詢語意可以直接映射到陣列運算子： ALTER TABLE customers ADD COLUMN tags_array text[];UPDATE customers SET tags_array = string_to_array(nullif(trim(tags), ''), ';');B. 建立 GIN 倒排索引 CREATE INDEX CONCURRENTLY idx_customers_tags_gin ON customers USING gin (tags_array);查詢改寫為陣列運算（如 &&, @>），讓 PostgreSQL 能走 GIN：tags_array && ARRAY['tag_VIP']::text[]：判斷是否有交集 tags_array @> ARRAY['tag_VIP']::text[]：判斷是否完整包含可帶走的觀念：之後遇到 DB 效能問題都還用得上的基本功 下面這幾個基本功，下次遇到任何資料庫效能問題都會再用到：量測優先於直覺：靠 EXPLAIN ANALYZE 看計畫，永遠比靠經驗或感覺猜瓶頸可靠。 用複雜度語言描述現況：把 N、M、K 這些變數寫出來，才有辦法理性比較不同寫法的代價。 資料模型是性能的天花板：SQL 語法只能在現有結構上做局部優化，要根本拉開差距常常得回頭改欄位設計。 優化要在 plan 上驗證：要看到 Seq Scan 真的換成 Index Scan 才算數，執行時間變短不代表存取路徑改了。結論Regex 的確能先把 CPU 壓力降下來，但資料量一大，真正拉開差距的還是 Array + GIN 這種直接改變查詢路徑的做法。回頭快速複習前兩集：上集：當 LIKE 遇上 Regex 的觀念碰撞 中集：用 EXPLAIN 看見 Seq Scan 的真相

Read More