針對大量資料優化函式

講到在 PostgreSQL 裡優化 function，通常會想到兩個重點：索引（index） 跟 分割（partitioning）。這兩個技巧可以讓你處理大量資料時更快，減少不必要的運算，直接命中你要的資料。來，細講一下。

資料庫裡的 index 跟書本的目錄很像。你找書裡的資訊，不會一頁一頁翻，會直接看目錄，找到主題就跳過去。PostgreSQL 的 index 也是這樣運作的。

建立索引

index 用 CREATE INDEX 指令來建。簡單例子：

-- 在 users 表的 id 欄位建立索引，加速查找
CREATE INDEX idx_users_id ON users (id);

現在，如果你執行像這樣的查詢：

SELECT * FROM users WHERE id = 42;

PostgreSQL 會用你剛剛建的 index，很快就找到那一行。

範例：用索引優化 function

假設我們有個 function，會根據 user 從 orders 表撈訂單資料：

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

如果 orders 表有幾百萬筆資料，這 function 執行會很慢。怎麼辦？在 user_id 建個 index：

CREATE INDEX idx_orders_user_id ON orders (user_id);

這樣 function 裡的查詢就會快很多，因為 PostgreSQL 會用 index 來找資料。

索引的種類

PostgreSQL 支援好幾種 index，最常用的是 B-TREE 跟 GIN。簡單比較一下：

想更深入了解 index，可以去看 PostgreSQL 官方文件。

資料分割（Partitioning）

index 是加速查找，partitioning 則是把大表拆成小塊（partition）。如果你有一堆資料都塞在同一張表，這就很有用。

假設你有個 orders 表，存了過去 10 年的訂單。如果你只想查最近一個月的訂單，PostgreSQL 還是會掃整張表，這很慢。partitioning 可以把資料按年份分開。

建立分割表

這樣可以建立 partition table：

-- 建立 orders 父表，作為 partition
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- 為每一年建立子表
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

現在你查詢像這樣：

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQL 會直接去 orders_2023 查，不用掃整張表。

在 function 裡用 partitioning

假設我們有個 function，會查某一年的訂單。因為有 partitioning，function 裡的查詢會快很多，因為 PostgreSQL 只會查對應的子表。

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

實戰案例

1. 索引案例

字串查找：如果你有商品表，常常要用名字查商品，就在 name 欄位建個 index：

CREATE INDEX idx_products_name ON products (name);

加速排序：如果查詢常常要用日期排序，也可以建個 index：

CREATE INDEX idx_orders_date ON orders (order_date);

2. 分割案例

歷史資料：如果表裡有時間戳記，用天、月、年來分割，查詢會快很多。

地理資料：如果表裡有國家資料，可以每個國家一個 partition。

常見錯誤跟解法

很多工程師會犯一個錯，就是建太多 index。這會讓 insert 跟 update 變慢，因為 PostgreSQL 每次改表都要更新 index。建議：只在你常查詢或排序的欄位建 index。

另一個常見錯誤是 partitioning 分太細。比如你用天來分而不是用月，這樣會讓管理變很麻煩，效能反而不好。