針對大量資料的分析函數優化：索引與分區

當資料變多（就像公司群組裡的 deadline 訊息一樣多）時，查詢跟處理速度就會變慢。主要原因有這幾個：

1. 沒有索引。當 PostgreSQL 執行查詢時必須掃描整個表（這叫做 "Seq Scan" — 順序掃描），查詢就會變超慢。
2. SQL 查詢寫得不夠有效率。如果查詢沒考慮到優化，就算有 index 也可能遇到效能瓶頸。比如你忘了在 WHERE 用關鍵條件？那就等著查詢跑很久吧。
3. 一張表裡資料量太大。像是你想一次分析所有年份的銷售紀錄，就算有 index 也救不了你。

別擔心，我們有兩個超實用的招式：索引（Indexing）跟分區（Partitioning）。

用索引加速查詢

這裡有個簡單的 index 建立範例：

CREATE INDEX idx_sales_date ON sales(transaction_date);

• 這裡 idx_sales_date 是 index 的名字（你可以自己取，但最好有意義一點）。
• ON sales(transaction_date) — 指定要在哪個表、哪個欄位建立 index。

如果你常常用 transaction_date 這個欄位查詢，這個 index 就超有用。

這種查詢就會因為這個 index 跑更快：

SELECT *
FROM sales
WHERE transaction_date BETWEEN '2023-01-01' AND '2023-12-31';

複合鍵索引

如果你的查詢常常同時用到好幾個欄位，比如 region 跟 product_id，可以考慮建立複合 index：

CREATE INDEX idx_sales_region_product ON sales(region, product_id);

像這種查詢就會快很多：

SELECT *
FROM sales
WHERE region = '北美' AND product_id = 42;

唯一索引

Unique index 不只加速查詢，還能保證欄位值唯一。舉例：

CREATE UNIQUE INDEX idx_unique_customer_email ON customers(email);

這樣你就不會不小心讓兩個客戶用同一個 email 了。

分析函數的索引

有些分析用的函數，像 SUM、COUNT 或 AVG，可以用 index 來加速計算。舉個例子：

CREATE INDEX idx_sales_amount ON sales(amount);

查詢：

SELECT SUM(amount)
FROM sales 
WHERE transaction_date >= '2023-01-01';

因為有 index，這查詢會跑更快。

用分區表處理大量資料

分區（Partitioning）就是把一個大表拆成比較小的邏輯區塊，這些區塊叫 partition。比如你可以把 sales 這張表依年份分成 sales_2021、sales_2022 之類的。

你覺得很難嗎？其實 PostgreSQL 做起來比你想像的簡單。

分區的類型

1. 區間分區（Range Partitioning）。根據範圍（像日期）來分。
2. 清單分區（List Partitioning）。根據明確的值（像地區）來分。
3. 雜湊分區（Hash Partitioning）。用 hash function 來分（這種比較少手動用）。

建立分區表

來建立一個依年份分區的銷售表吧。

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    transaction_date DATE NOT NULL,
    amount NUMERIC,
    region TEXT
) PARTITION BY RANGE (transaction_date);

現在來為不同年份建立 partition：

CREATE TABLE sales_2021 PARTITION OF sales
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE sales_2022 PARTITION OF sales
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

查詢時只要有日期條件，PostgreSQL 會自動只查需要的 partition。你可以用 EXPLAIN 指令來驗證。

分區範例

這是只計算 2021 年銷售總額的查詢：

SELECT SUM(amount)
FROM sales
WHERE transaction_date BETWEEN '2021-01-01' AND '2021-12-31';

你看，PostgreSQL 只會查 sales_2021 這個 partition，不會掃整張表。

範例：優化地區指標計算

假設你想算每個地區的銷售總額。沒 index、沒 partition 的話會慢到爆。先幫 region 欄位加個 index：

CREATE INDEX idx_sales_region ON sales(region);

你的查詢：

SELECT region, SUM(amount)
FROM sales
GROUP BY region;

有 index 之後，查詢就快多了。

範例：時間資料分區

像交易或 log 這種時間資料，可以每個月分一個 partition。舉例：

CREATE TABLE sales_monthly PARTITION BY RANGE (transaction_date);

CREATE TABLE sales_jan_2023 PARTITION OF sales_monthly
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

查詢：

SELECT SUM(amount)
FROM sales_monthly
WHERE transaction_date >= '2023-01-01' AND transaction_date < '2023-02-01';

會更快，因為 PostgreSQL 只會讀 sales_jan_2023 這個 partition。

範例：索引加分區一起用

索引跟分區可以一起用，效能會更好。你可以在每個 partition 裡再加 index。像這樣：

CREATE INDEX idx_sales_amount_jan_2023 ON sales_jan_2023(amount);

怎麼避免常見錯誤

很多效能問題都是 index 跟 partition 用錯造成的。舉例：

• index 太多會讓 insert 變慢。
• partition 要設計得剛剛好，太小或太大都會拖慢效能。
• 沒用 EXPLAIN ANALYZE 先看效能就亂優化，就像修車不打開引擎蓋一樣瞎。

記得隨時檢查你的優化到底有沒有加速，不要怕多試幾種方法。