分析建立分析程序時常見的錯誤

今天，為了結束我們這趟 PL/pgSQL 史詩級旅程，先搞清楚一件事：分析程序裡出錯是無可避免的。為什麼？因為分析就是要處理大數據、複雜計算，還有時不時很 tricky 的條件。查詢或程序越複雜，就越像走迷宮，一步走錯就會出現奇怪的結果。

幸好，大部分錯誤都很典型，可以預測（也能預防）。我們一個一個來看。

1. 關鍵欄位沒加 index

Index 就像資料庫世界的導航。如果沒有，資料庫就只能一條一條慢慢走過整張表。小表還能忍，但資料一多到幾百萬筆，你的查詢就會比 Windows XP 跑在 Pentium III 還慢。

假設你有一張訂單表，要算上個月的銷售額：

SELECT SUM(order_total)
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month';

如果 order_date 欄位沒 index，PostgreSQL 會做全表掃描（Seq Scan）。這幾乎都很慢。

解法：加 index！只要這樣：

CREATE INDEX idx_order_date ON orders (order_date);

現在 PostgreSQL 查 order_date 就快多了。

使用低效查詢

有些查詢看起來很美，但用起來像用磚塊開門。例如用 subquery，其實可以用 join，或是多餘的 filter。

像這樣：

SELECT product_id, SUM(order_total)
FROM orders
WHERE product_id IN (SELECT id FROM products WHERE category = 'electronics')
GROUP BY product_id;

其實這樣比較好：

SELECT o.product_id, SUM(o.order_total)
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE p.category = 'electronics'
GROUP BY o.product_id;

這樣 PostgreSQL 就不用每一行都跑 subquery，速度快很多。

臨時表結構設計錯誤

臨時表用得好很強大，但如果忘了加需要的欄位或 index，臨時表就會變成瓶頸，整個程序都慢下來。

舉個例子，建立一個臨時表做中間計算：

CREATE TEMP TABLE temp_sales AS
SELECT region, SUM(order_total) AS total_sales
FROM orders
GROUP BY region;

但之後你要用 total_sales 欄位做 filter，卻沒 index。

用臨時表前，先想想你會怎麼用。如果要 filter，記得加 index：

CREATE INDEX idx_temp_sales_total_sales ON temp_sales (total_sales);

計算錯誤（例如除以零）

除以零是分析界的經典地雷。SQL 不會幫你遮掩這種錯，他會直接讓查詢爆掉。

比如你想算訂單平均金額：

SELECT SUM(order_total) / COUNT(*) AS avg_order_value
FROM orders;

如果 orders 表沒資料，就會除以零，查詢直接錯誤。

避免這種情況，可以這樣寫：

SELECT
    CASE 
        WHEN COUNT(*) = 0 THEN 0
        ELSE SUM(order_total) / COUNT(*)
    END AS avg_order_value
FROM orders;

沒做 log 跟執行監控

PL/pgSQL 程序常常很複雜，分好幾個步驟：從中間計算到最後報表。如果哪個環節出錯，沒 log 你根本不知道哪裡出問題。

比如你寫一個算指標的程序，卻沒檢查每一步的資料。結果遇到怪資料（像空表）整個程序就掛了。

避免這種事，可以在每個重要步驟加 log。例如：

RAISE NOTICE '開始計算銷售額';
-- 你的程式碼...

RAISE NOTICE '模組 % 成功完成', 模組;

複雜一點的程序，建議把 log 存到專用表：

CREATE TABLE log_analytics (
    log_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    log_message TEXT
);

在程序裡加：

INSERT INTO log_analytics (log_message)
VALUES ('程序成功完成');

沒優化導致效能問題

優化不只查詢本身，程序也要顧。如果很多人用同一個程序，執行起來就會變成系統瓶頸。

像這個例子，這個程序會幫所有地區重算指標，其實你只要一個地區的資料：

CREATE OR REPLACE FUNCTION calculate_sales()
RETURNS VOID AS $$
BEGIN
    -- 所有地區都重算
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

這樣會造成不必要的負擔。

怎麼辦？加個參數讓你可以指定地區：

CREATE OR REPLACE FUNCTION calculate_sales(p_region TEXT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO sales_metrics(region, total_sales)
    SELECT region, SUM(order_total)
    FROM orders
    WHERE region = p_region
    GROUP BY region;
END;
$$ LANGUAGE plpgsql;

這樣程序就不會處理多餘的資料，查詢也快多了。

忽略效能分析工具

像 EXPLAIN ANALYZE 這種工具超好用，可以讓你知道查詢卡在哪裡、怎麼救。如果你寫程序卻不分析效能，就像量子電腦工程師沒帶示波器——好像有跑，但到底怎麼回事誰都不知道。

舉個例子，這個查詢的問題用 EXPLAIN ANALYZE 一看就知道：

SELECT *
FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2023;

這查詢很慢，因為 EXTRACT() 會讓 index 失效。

解法如下。先用工具分析，再改寫查詢：

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE order_date >= DATE '2023-01-01' AND order_date < DATE '2024-01-01';

怎麼避免常見錯誤？

想預防錯誤，記得這幾點：

1. 在 filter 或 join 用到的欄位加 index。
2. 優化查詢：減少多餘的 subquery，多用 JOIN。
3. 加 log，debug 起來超方便。
4. 常用 EXPLAIN ANALYZE 這類工具檢查你的程序。
5. 發現效能問題？可以考慮 partition 或重寫查詢邏輯。

現在你已經有知識武裝自己，能預測和避免那些會讓分析師沒咖啡機、沒 Wi-Fi 的慢查詢了。