PostgreSQL 高級查詢與性能優化實戰 2026 | 數據庫性能完全指南

PostgreSQL 是功能最強大的開源關係型數據庫之一。當數據量增長後，掌握高級查詢技巧和性能優化方法成為後端工程師的必備能力。本文將系統講解窗口函數、CTE 遞歸、索引策略、執行計劃分析及分區表等核心優化手段。

---

一、窗口函數（Window Functions）

1.1 基本語法

-- 窗口函數語法
function_name() OVER (
  [PARTITION BY column1, column2, ...]
  [ORDER BY column3 [ASC|DESC]]
  [frame_clause]
)

1.2 排名函數

-- 員工表
CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  department VARCHAR(50),
  salary NUMERIC(10, 2),
  hire_date DATE
);

-- 按部門排名薪資（相同薪資排名相同，跳號）
SELECT
  name,
  department,
  salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num
FROM employees
ORDER BY department, rank_in_dept;

-- 結果示例：
-- name    | department | salary | rank_in_dept | dense_rank | row_num
-- Alice   | Engineering | 120000 |      1       |     1      |   1
-- Bob     | Engineering | 110000 |      2       |     2      |   2
-- Charlie | Engineering | 110000 |      2       |     2      |   3
-- Dave    | Engineering | 100000 |      4       |     3      |   4

1.3 聚合窗口函數

-- 累計薪資（從入職最早到當前員工）
SELECT
  name,
  department,
  salary,
  hire_date,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_salary,
  -- 部門平均薪資
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  -- 移動平均（當前行和前後各一行）
  AVG(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS moving_avg
FROM employees;

1.4 偏移函數

-- 與前一名員工的薪資差距
SELECT
  name,
  department,
  salary,
  LAG(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS prev_salary,
  salary - LAG(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS salary_diff,
  -- 下一名員工薪資
  LEAD(salary, 1) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS next_salary,
  -- 部門第一/最後薪資
  FIRST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
  ) AS highest_in_dept,
  LAST_VALUE(salary) OVER (
    PARTITION BY department ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_in_dept
FROM employees;

1.5 分桶函數

-- 將薪資分為 4 個等級
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

-- 結果：
-- name  | salary | salary_quartile
-- Alice | 120000 |       1
-- Bob   | 110000 |       1
-- ...   |  ...   |       2
-- Dave  |  80000 |       4

---

二、CTE 與遞歸查詢

2.1 普通 CTE

-- 查詢各部門薪資高於部門平均的員工
WITH dept_avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.name,
  e.department,
  e.salary,
  d.avg_salary
FROM employees e
JOIN dept_avg d ON e.department = d.department
WHERE e.salary > d.avg_salary
ORDER BY e.department, e.salary DESC;

2.2 多 CTE 組合

-- 綜合統計：部門人數、平均薪資、最高薪資、高薪人數
WITH
  dept_stats AS (
    SELECT
      department,
      COUNT(*) AS emp_count,
      AVG(salary) AS avg_salary,
      MAX(salary) AS max_salary
    FROM employees
    GROUP BY department
  ),
  high_earners AS (
    SELECT
      department,
      COUNT(*) AS high_count
    FROM employees
    WHERE salary > 100000
    GROUP BY department
  )
SELECT
  d.department,
  d.emp_count,
  ROUND(d.avg_salary, 2) AS avg_salary,
  d.max_salary,
  COALESCE(h.high_count, 0) AS high_earners_count
FROM dept_stats d
LEFT JOIN high_earners h ON d.department = h.department
ORDER BY d.avg_salary DESC;

2.3 遞歸 CTE

-- 組織架構樹
CREATE TABLE org_chart (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  manager_id INTEGER REFERENCES org_chart(id)
);

-- 遞歸查詢：從 CEO 開始遍歷整個組織樹
WITH RECURSIVE org_tree AS (
  -- 基礎查詢：找到根節點（CEO）
  SELECT
    id,
    name,
    manager_id,
    0 AS depth,
    name::TEXT AS path,
    ARRAY[name] AS name_path
  FROM org_chart
  WHERE manager_id IS NULL

  UNION ALL

  -- 遞歸查詢：找到下屬
  SELECT
    o.id,
    o.name,
    o.manager_id,
    ot.depth + 1,
    ot.path || ' > ' || o.name,
    ot.name_path || o.name
  FROM org_chart o
  JOIN org_tree ot ON o.manager_id = ot.id
)
SELECT
  REPEAT('  ', depth) || name AS org_structure,
  depth,
  path
FROM org_tree
ORDER BY name_path;

-- 結果示例：
--   org_structure  | depth | path
--   CEO            |   0   | CEO
--     VP-Eng       |   1   | CEO > VP-Eng
--       Eng-Mgr-1  |   2   | CEO > VP-Eng > Eng-Mgr-1
--       Eng-Mgr-2  |   2   | CEO > VP-Eng > Eng-Mgr-2
--     VP-Sales     |   1   | CEO > VP-Sales

2.4 遞歸查詢：層級評論

-- 評論表
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER,
  parent_id INTEGER REFERENCES comments(id),
  author VARCHAR(100),
  content TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);

-- 查詢某帖子的評論樹
WITH RECURSIVE comment_tree AS (
  SELECT
    id,
    post_id,
    parent_id,
    author,
    content,
    created_at,
    0 AS depth,
    ARRAY[id] AS path
  FROM comments
  WHERE post_id = 42 AND parent_id IS NULL

  UNION ALL

  SELECT
    c.id,
    c.post_id,
    c.parent_id,
    c.author,
    c.content,
    c.created_at,
    ct.depth + 1,
    ct.path || c.id
  FROM comments c
  JOIN comment_tree ct ON c.parent_id = ct.id
  WHERE c.post_id = 42
)
SELECT
  REPEAT('  ', depth) || author || ': ' || LEFT(content, 50) AS comment_thread,
  depth,
  created_at
FROM comment_tree
ORDER BY path, created_at;

---

三、索引優化

3.1 索引類型

-- 1. B-Tree 索引（默認，適合等值查詢和範圍查詢）
CREATE INDEX idx_emp_salary ON employees(salary);
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- 2. Hash 索引（僅等值查詢，不支持範圍）
CREATE INDEX idx_emp_name_hash ON employees USING HASH(name);

-- 3. GIN 索引（全文搜索、JSONB、數組）
CREATE INDEX idx_products_tags ON products USING GIN(tags);
CREATE INDEX idx_products_meta ON products USING GIN(metadata jsonb_path_ops);

-- 4. GiST 索引（幾何類型、範圍類型）
CREATE INDEX idx_stores_location ON stores USING GIST(location);

-- 5. BRIN 索引（大表、有序數據，佔用極小）
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

-- 6. 部分索引（只索引滿足條件的行）
CREATE INDEX idx_active_users ON users(email) WHERE active = true;

-- 7. 表達式索引
CREATE INDEX idx_emp_lower_name ON employees(LOWER(name));

-- 8. 覆蓋索引（INCLUDE 包含額外列）
CREATE INDEX idx_emp_dept ON employees(department) INCLUDE (salary, name);

3.2 複合索引策略

-- 複合索引的列順序至關重要
-- 遵循：等值條件在前，範圍條件在後

-- 場景：查詢某部門薪資大於 10 萬的員工
-- 查詢：WHERE department = 'Engineering' AND salary > 100000

-- 好的索引：先等值（department），再範圍（salary）
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- 差的索引：範圍在前，等值在後無法利用索引
-- CREATE INDEX idx_emp_salary_dept ON employees(salary, department); -- 效果差

-- 驗證索引使用情況
EXPLAIN ANALYZE
SELECT * FROM employees
WHERE department = 'Engineering' AND salary > 100000;

3.3 索引維護

-- 查看索引使用統計
SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan AS scans,
  idx_tup_read AS tuples_read,
  idx_tup_fetch AS tuples_fetched,
  pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 查找未使用的索引
SELECT
  schemaname,
  relname,
  indexrelname,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 重建索引（在線重建，不鎖表）
REINDEX INDEX CONCURRENTLY idx_emp_dept_salary;

-- 分析表統計信息
ANALYZE employees;

---

四、EXPLAIN 執行計劃分析

4.1 EXPLAIN 基礎

-- 查看執行計劃
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering';

-- 查看執行計劃 + 實際執行
EXPLAIN ANALYZE SELECT * FROM employees WHERE department = 'Engineering';

-- 查看執行計劃 + 實際執行 + 緩衝區
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM employees WHERE department = 'Engineering';

-- JSON 格式輸出
EXPLAIN (FORMAT JSON)
SELECT * FROM employees WHERE department = 'Engineering';

4.2 常見掃描類型

-- 1. Seq Scan（全表掃描）— 通常需要優化
EXPLAIN SELECT * FROM employees WHERE salary > 50000;
-- Seq Scan on employees  (cost=0.00..35.50 rows=800 width=...)
--   Filter: (salary > 50000)

-- 2. Index Scan（索引掃描）— 索引+回表
EXPLAIN SELECT * FROM employees WHERE id = 100;
-- Index Scan using employees_pkey on employees  (cost=0.15..8.17 rows=1)
--   Index Cond: (id = 100)

-- 3. Index Only Scan（僅索引掃描）— 覆蓋索引
EXPLAIN SELECT department, salary FROM employees WHERE department = 'Engineering';
-- Index Only Scan using idx_emp_dept on employees  (cost=0.15..25.36)
--   Index Cond: (department = 'Engineering')

-- 4. Bitmap Index Scan → Bitmap Heap Scan（位圖掃描）
EXPLAIN SELECT * FROM employees WHERE department = 'Engineering' AND salary > 100000;
-- Bitmap Heap Scan on employees  (cost=8.30..20.15)
--   Recheck Cond: (department = 'Engineering')
--   Filter: (salary > 100000)
--   -> Bitmap Index Scan on idx_emp_dept  (cost=0.00..8.30)
--        Index Cond: (department = 'Engineering')

4.3 連接類型

-- Nested Loop（嵌套循環）— 適合小表驅動大表
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
WHERE c.city = 'Tokyo';

-- Hash Join（哈希連接）— 適合大表等值連接
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN products p ON o.product_id = p.id;

-- Merge Join（合併連接）— 需要兩端有序
EXPLAIN ANALYZE
SELECT * FROM orders o JOIN customers c ON o.customer_id = c.id
ORDER BY c.id;

4.4 關鍵指標解讀

EXPLAIN ANALYZE 輸出示例：

Index Scan using idx_emp_dept on employees  (cost=0.42..25.36 rows=50 width=...) (actual time=0.015..0.318 rows=48 loops=1)
  Index Cond: (department = 'Engineering')

關鍵指標：
- cost: 估算成本（啟動..總成本）
- rows: 估算行數
- actual time: 實際執行時間（啟動..總時間）
- rows (actual): 實際返回行數
- loops: 循環次數

優化判斷：
1. 估算行數 vs 實際行數差距大 → 需要 ANALYZE
2. Seq Scan 在大表上 → 需要加索引
3. actual time 很高 → 重點優化該節點
4. Filter 過濾了大量行 → 索引策略有問題

---

五、分區表

5.1 範圍分區

-- 按日期範圍分區（日誌表）
CREATE TABLE logs (
  id BIGSERIAL,
  created_at TIMESTAMP NOT NULL,
  level VARCHAR(20),
  message TEXT,
  metadata JSONB
) PARTITION BY RANGE (created_at);

-- 創建月度分區
CREATE TABLE logs_2026_01 PARTITION OF logs
  FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE logs_2026_02 PARTITION OF logs
  FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

CREATE TABLE logs_2026_03 PARTITION OF logs
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

-- 默認分區（容納不屬於任何分區的數據）
CREATE TABLE logs_default PARTITION OF logs DEFAULT;

-- 在分區上創建索引（自動傳播到所有分區）
CREATE INDEX idx_logs_level ON logs(level);
CREATE INDEX idx_logs_created ON logs(created_at);

5.2 列表分區

-- 按地區分區
CREATE TABLE orders (
  id BIGSERIAL,
  region VARCHAR(50) NOT NULL,
  amount NUMERIC(12, 2),
  status VARCHAR(20),
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE orders_asia PARTITION OF orders
  FOR VALUES IN ('China', 'Japan', 'Korea', 'Singapore');

CREATE TABLE orders_europe PARTITION OF orders
  FOR VALUES IN ('Germany', 'France', 'UK', 'Spain');

CREATE TABLE orders_americas PARTITION OF orders
  FOR VALUES IN ('USA', 'Canada', 'Brazil', 'Mexico');

5.3 哈希分區

-- 按哈希均勻分佈
CREATE TABLE user_events (
  id BIGSERIAL,
  user_id BIGINT NOT NULL,
  event_type VARCHAR(50),
  event_data JSONB,
  created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

-- 創建 4 個分區
CREATE TABLE user_events_0 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE user_events_1 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 1);
CREATE TABLE user_events_2 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 2);
CREATE TABLE user_events_3 PARTITION OF orders
  FOR VALUES WITH (modulus 4, remainder 3);

5.4 分區管理自動化

-- 使用 pg_partman 擴展自動管理分區
CREATE EXTENSION IF NOT EXISTS pg_partman;

-- 創建自動分區策略
SELECT partman.create_parent(
  p_parent_table => 'public.logs',
  p_control => 'created_at',
  p_type => 'range',
  p_interval => 'monthly',
  p_premake => 3  -- 預創建未來 3 個月的分區
);

-- 定期運行維護函數（通過 cron）
-- SELECT partman.run_maintenance_proc();

---

六、查詢優化實戰

6.1 避免 SELECT *

-- 差：查詢所有列
SELECT * FROM employees WHERE department = 'Engineering';

-- 好：只查詢需要的列
SELECT id, name, salary FROM employees WHERE department = 'Engineering';

6.2 批量操作優化

-- 差：逐行插入
INSERT INTO orders (customer_id, amount) VALUES (1, 100);
INSERT INTO orders (customer_id, amount) VALUES (2, 200);
INSERT INTO orders (customer_id, amount) VALUES (3, 300);

-- 好：批量插入
INSERT INTO orders (customer_id, amount) VALUES
  (1, 100), (2, 200), (3, 300);

-- 使用 COPY 導入大量數據
COPY orders FROM '/path/to/orders.csv' WITH (FORMAT csv, HEADER true);

-- 使用 UPSERT 處理衝突
INSERT INTO products (id, name, price)
VALUES (1, 'Widget', 29.99)
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  price = EXCLUDED.price,
  updated_at = NOW();

6.3 子查詢優化

-- 差：相關子查詢（每行執行一次子查詢）
SELECT
  name,
  (SELECT AVG(salary) FROM employees e2 WHERE e2.department = e1.department) AS dept_avg
FROM employees e1;

-- 好：JOIN + 聚合
SELECT
  e.name,
  d.avg_salary AS dept_avg
FROM employees e
JOIN (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) d ON e.department = d.department;

6.4 EXISTS vs IN

-- 大子表用 EXISTS 更高效
SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi
  WHERE oi.order_id = o.id
  AND oi.product_id = 42
);

-- 小子表用 IN 更簡潔
SELECT * FROM orders o
WHERE o.customer_id IN (
  SELECT id FROM customers WHERE city = 'Tokyo'
);

6.5 分頁優化

-- 差：OFFSET 在大偏移量時極慢
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 100000;

-- 好：使用遊標分頁（keyset pagination）
SELECT * FROM orders
WHERE created_at < '2026-07-01 00:00:00'
ORDER BY created_at DESC
LIMIT 20;

---

七、服務器配置優化

7.1 核心參數

# postgresql.conf

# 內存相關
shared_buffers = 4GB                # 總內存的 25%
effective_cache_size = 12GB         # 總內存的 75%
work_mem = 64MB                     # 每個排序/哈希操作的內存
maintenance_work_mem = 512MB        # VACUUM/CREATE INDEX 內存

# WAL 相關
wal_buffers = 16MB
max_wal_size = 2GB
checkpoint_completion_target = 0.9
wal_compression = on

# 並行查詢
max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 100
parallel_tuple_cost = 0.1

# 自動清理
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 30s
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

# 連接池
max_connections = 100

7.2 連接池配置（PgBouncer）

# pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 300

---

八、監控與診斷

8.1 慢查詢日誌

# postgresql.conf
log_min_duration_statement = 100    # 記錄超過 100ms 的查詢
log_line_prefix = '%t [%p] %u@%d '
log_checkpoints = on
log_connections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

8.2 pg_stat_statements

-- 啟用擴展
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 查看最慢的查詢
SELECT
  query,
  calls,
  total_exec_time,
  mean_exec_time,
  rows,
  100.0 * shared_blks_hit /
    NULLIF(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 查看最頻繁的查詢
SELECT
  LEFT(query, 80) AS query,
  calls,
  total_exec_time,
  mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

8.3 鎖等待分析

-- 查看鎖等待
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query,
  blocked.mode AS blocked_mode,
  blocking.mode AS blocking_mode
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
  ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));

-- 終止阻塞進程
-- SELECT pg_terminate_backend(<blocking_pid>);

---

九、總結

• ✅ 窗口函數（排名、聚合、偏移、分桶）
• ✅ CTE 與遞歸查詢（組織樹、評論樹）
• ✅ 索引優化（7 種索引類型、複合索引策略、索引維護）
• ✅ EXPLAIN 執行計劃分析（掃描類型、連接類型、關鍵指標）
• ✅ 分區表（範圍、列表、哈希分區、自動管理）
• ✅ 查詢優化實戰（批量操作、子查詢、分頁）
• ✅ 服務器配置優化（內存、WAL、並行查詢、連接池）
• ✅ 監控與診斷（慢查詢、pg_stat_statements、鎖等待）

PostgreSQL 性能優化是一個系統工程，從查詢語句到索引策略再到服務器配置，每一層都需要精細調優。

---

相關閱讀：

• Redis 緩存策略與分佈式鎖實戰
• Node.js 性能調優與內存洩漏排查
• Kubernetes 入門與生產部署實戰
• TypeScript 項目架構與工程化實踐
• Web 安全防護與 XSS/CSRF 防禦實戰