WHERE 句でのフィルタリング
PostgreSQLでクエリ性能を改善する
Amy McCarty
Instructor
データを絞り込む
| Order | Clause | Purpose |
|---|---|---|
| 1 | FROM | テーブルを指定 |
| 2 | WHERE | 行をフィルタ/制限 |
- 早い段階で実行
- 行数を減らす
EXPLAIN
EXPLAIN
SELECT * FROM phones
Seq Scan on phones (cost = 0.00..22.7
,rows=1270
,width=36)
- 実行ステップ数
クエリプランナー
WHERE 付きの EXPLAIN
EXPLAIN
SELECT * FROM phones
WHERE phone_code = 235
Seq Scan on phones (cost = 0.00..25.8
,rows=6,width=636)
Filter: (phone_code=235)
クエリプランナー
良い - LIKE OR で類似値をフィルタ
| country | phone_code | reliability |
|---|---|---|
| Chad | 235 | medium |
| China | 86 | high |
| Costa Rica | 506 | high |
| India | 91 | medium |
| Indonesia | 62 | medium |
| Iraq | 964 | low |
EXPLAIN
SELECT * FROM phones
WHERE country LIKE 'Ch%'
OR country LIKE 'In%'
Seq Scan on phones (cost = 0.00..29.05
,rows=13,width=36)
Filter: ((country~~'Ch%'::text)
OR(country~~'In%'::text))
より良い - LIKE ANY で類似値をフィルタ
| country | phone_code | reliability |
|---|---|---|
| Chad | 235 | medium |
| China | 86 | high |
| Costa Rica | 506 | high |
| India | 91 | medium |
| Indonesia | 62 | medium |
| Iraq | 964 | low |
EXPLAIN
SELECT * FROM phones
WHERE country
LIKE ANY(ARRAY['Ch%','In%'])
Seq Scan on phones (cost = 0.00..25.88
,rows=13,width=36)
Filter: ((country~~ANY('{Ch%,In%}'
::text[]))
良い - OR で完全一致をフィルタ
| country | phone_code | reliability |
|---|---|---|
| Chad | 235 | medium |
| China | 86 | high |
| Costa Rica | 506 | high |
| India | 91 | medium |
| Indonesia | 62 | medium |
| Iraq | 964 | low |
EXPLAIN
SELECT * FROM phones
WHERE country = 'Chad'
OR country = 'China'
Seq Scan on phones (cost = 0.00..29.05
,rows=13,width=36)
Filter: ((country='Chad'::text)
OR(country='China'::text))
より良い - IN で完全一致をフィルタ
| country | phone_code | reliability |
|---|---|---|
| Chad | 235 | medium |
| China | 86 | high |
| Costa Rica | 506 | high |
| India | 91 | medium |
| Indonesia | 62 | medium |
| Iraq | 964 | low |
EXPLAIN
SELECT * FROM phones
WHERE country IN ('Chad','China')
Seq Scan on phones (cost = 0.00..25.88
,rows=13,width=36)
Filter: ((country=ANY('{Chad,China}'
::text[]))
最良 - 数値でフィルタリング
| country | phone_code | reliability |
|---|---|---|
| Chad | 235 | medium |
| China | 86 | high |
| Costa Rica | 506 | high |
| India | 91 | medium |
| Indonesia | 62 | medium |
| Iraq | 964 | low |
EXPLAIN
SELECT *
FROM phones
WHERE phone_code IN (235,86)
Seq Scan on phones (cost = 0.00..25.88
,rows=13,width=36)
Filter: (phone_code=ANY('{235,86}'
::integer[]))
最良の WHERE フィルタのまとめ
数値の利点
- より短い
- より小さい保存サイズ
- パフォーマンス向上
最良の WHERE フィルタのまとめ
数値の利点
- より短い
- より小さい保存サイズ
- パフォーマンス向上
| 良い | より良い |
|---|---|
| 文字列 | 数値 |
| OR | IN, ARRAY |
Let's practice!
PostgreSQLでクエリ性能を改善する
