函式與程序效能分析：用 EXPLAIN ANALYZE

EXPLAIN ANALYZE 可以幫你搞懂 PostgreSQL 執行查詢時「在想什麼」：

• 處理資料時會做哪些步驟。
• 每個步驟花多少時間。
• 為什麼某個查詢很慢——可能是整張表都掃過（英文叫 Seq Scan），或是 index 沒用上。

EXPLAIN ANALYZE 這個指令其實會真的執行查詢 而且 會秀給你看 PostgreSQL 怎麼優化執行過程。想像你在拆開手錶，想看裡面的機械怎麼動。EXPLAIN ANALYZE 就是在幫你拆 SQL 查詢的機械。

EXPLAIN ANALYZE 語法

先從簡單的開始。這是最基本的指令長相：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

這個查詢會執行 SELECT，然後秀出 PostgreSQL 怎麼處理資料。

EXPLAIN ANALYZE 的結果是一棵查詢執行樹。每一層都描述 PostgreSQL 做的某個步驟：

• Operation Type — 操作類型（像 Seq Scan、Index Scan 這種）。
• Cost — PostgreSQL 覺得這個操作「多貴」。
• Rows — 預期會有幾列，實際結果有幾列。
• Time — 這個步驟花了多少時間。

輸出範例：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

注意 Seq Scan on students。這代表 PostgreSQL 會把 students 這張表的所有資料都掃過。如果這張表很大，會超級慢。

EXPLAIN ANALYZE 實戰範例

來看幾個實戰例子，教你怎麼找出查詢的問題並解決。

範例 1：整張表都掃過

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

輸出：

Seq Scan on students  (cost=0.00..35.50 rows=1000 width=64) (actual time=0.023..0.045 rows=250 loops=1)
  Filter: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.234 ms

問題在於 PostgreSQL 做了 Seq Scan，也就是整張表都掃過。如果表裡有幾百萬筆資料，這就會變成效能瓶頸。

解法：幫 age 欄位加個 index。

CREATE INDEX idx_students_age ON students(age);

現在再執行一次同樣的查詢：

EXPLAIN ANALYZE
SELECT * FROM students WHERE age > 20;

輸出：

Index Scan using idx_students_age on students  (cost=0.29..12.30 rows=250 width=64) (actual time=0.005..0.014 rows=250 loops=1)
  Index Cond: (age > 20)
Planning Time: 0.123 ms
Execution Time: 0.045 ms

你會看到 Index Scan，不是 Seq Scan 了。爽啦，查詢變超快！

範例 2：有 JOIN 的複雜查詢

假設我們有兩張表：students 跟 courses。我們想知道學生的名字和他們選的課程名稱。

EXPLAIN ANALYZE
SELECT s.name, c.course_name
FROM students s
JOIN enrollments e ON s.id = e.student_id
JOIN courses c ON e.course_id = c.id;

輸出可能長這樣：

Nested Loop  (cost=1.23..56.78 rows=500 width=128) (actual time=0.123..2.345 rows=500 loops=1)
  -> Seq Scan on students s  (cost=0.00..12.50 rows=1000 width=64) (actual time=0.023..0.045 rows=1000 loops=1)
  -> Index Scan using idx_enrollments_student_id on enrollments e  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
  -> Index Scan using idx_courses_id on courses c  (cost=0.29..2.345 rows=3 width=64) (actual time=0.005..0.014 rows=3 loops=1000)
Execution Time: 2.456 ms

你看，PostgreSQL 很聰明：有用 enrollments 跟 courses 的 index，查詢速度很快。如果有哪個 index 沒有，可能就會看到 Seq Scan，那就會慢下來。

函式效能優化

假設我們有個函式，會回傳大於某個年齡的學生名單：

CREATE OR REPLACE FUNCTION get_students_older_than(min_age INT)
RETURNS TABLE(id INT, name TEXT) AS $$
BEGIN
  RETURN QUERY
  SELECT id, name
  FROM students
  WHERE age > min_age;
END;
$$ LANGUAGE plpgsql;

我們可以用 EXPLAIN ANALYZE 來分析這個函式的效能：

EXPLAIN ANALYZE
SELECT * FROM get_students_older_than(20);

讓函式跑更快

如果這個函式執行很慢，通常是因為整張表都掃過。要解決這個問題：

1. 確認你在 filter 用到的欄位（age）有加 index。
2. 檢查表裡有多少資料，如果太多可以考慮分區（partitioning）。

瓶頸與解法

1. 整張表都掃過（Seq Scan）。 用 index 來加速查詢。但要注意，index 太多會讓 insert 變慢。

2. 查詢結果太多列。 如果查詢會回傳幾百萬筆資料，考慮加 filter（WHERE、LIMIT）或用分頁（OFFSET）。

3. 「很貴」的操作。 像排序、聚合或合併大表這種操作會吃很多資源。可以用 index，或把查詢拆成幾個步驟。