查詢生命週期與規劃器
改進 PostgreSQL 的查詢效能
Amy McCarty
Instructor
基本查詢生命週期
| 系統 | 前端步驟 | 後端處理 | |
|---|---|---|---|
| 1 | Parser | 將查詢送到資料庫 | 檢查語法。依系統規則將 SQL 轉成較易處理的語法。 |
| 2 | Planner & Optimizer | 評估並最佳化查詢工作 | 用資料庫統計建立查詢計畫。計算成本並選最佳計畫。 |
| 3 | Executor | 回傳查詢結果 | 依查詢計畫執行查詢。 |
查詢規劃器與最佳化器
隨 SQL 結構變動
- 產生計畫樹
- 節點對應各步驟
- 用 EXPLAIN 視覺化
- 估計每棵樹的成本
- 來自 pg_tables 的統計
- 以時間為基準的最佳化
1 Plan tree: https://www.postgresql.org/docs/current/querytree.html
來自 pg_tables 的統計
SELECT * FROM pg_class
WHERE relname = 'mytable'
-- sample of output columns
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- sample of output columns
null_frac | avg_width | n_distinct |
- 欄位索引
- 計算 null 數量
- 欄位寬度
- 不重複值數
EXPLAIN
- 查詢計畫視窗
- 步驟與成本「估計」
- 不實際執行查詢
- 對 cheeses 資料表做循序掃描
- 成本與大小估計
EXPLAIN
SELECT * FROM cheeses
Seq Scan on cheeses
(cost=0.00..10.50 rows=5725 width=296)
EXPLAIN：掃描
- 查詢計畫步驟
- 回傳資料列
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- Seq Scan：掃描資料表的所有列
EXPLAIN：成本
- 無量綱
- 比較輸出相同的結構
- 不應比較輸出不同的查詢
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- 0.00..：啟動時間
..10.50：總時間
總時間 = 啟動時間 + 執行時間
EXPLAIN：大小
- 大小估計
Seq Scan on cheeses (cost=0.00..10.50 rows=5725 width=296)
- rows：執行時需檢視的列數
- width：每列的位元組寬度
含 WHERE 子句的 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep')
Seq Scan on cheeses (cost=0.00..378.90 rows=3 width=118)
-> Filter: (species = ANY ('{"goat","sheep"}'::text[]))
- 由下而上
- 步驟 1：Filter
- 步驟 2：Sequential scan
- WHERE 子句
- 減少要掃描的列數並提高總成本
含索引的 EXPLAIN
EXPLAIN
SELECT * FROM cheeses WHERE species IN ('goat','sheep') -- index on species column
Bitmap Index Scan using species_idx on cheeses (cost=0.29..12.66 rows=3 width=118)
Index Cond: (species = ANY ('{"goat","sheep"}'::text[]))
- 步驟 1：Bitmap Index Scan
- Index Cond 說明掃描步驟
- INDEX
- 啟動成本由 0 增加
- 總成本由 379 降低
一起來練習吧！
改進 PostgreSQL 的查詢效能
