クエリのライフサイクルとプランナー
PostgreSQLでクエリ性能を改善する
Amy McCarty
Instructor
基本的なクエリのライフサイクル
| システム | フロントエンドの手順 | バックエンドの処理 | |
|---|---|---|---|
| 1 | Parser | クエリをDBへ送信 | 構文を検査。システムの規則に基づきSQLを機械向け表現へ変換。 |
| 2 | Planner & Optimizer | クエリを評価・最適化 | DB統計に基づきクエリ計画を作成。コストを算出し最適な計画を選択。 |
| 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'
-- 出力列の例
| relname | relhasindex |
SELECT * FROM pg_stats
WHERE tablename = 'mytable'
-- 出力列の例
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でクエリ性能を改善する
