在 WHERE 子句中过濾

改進 PostgreSQL 的查詢效能

Amy McCarty

Instructor

限制資料量

順序	子句	目的
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

一起來練習吧！

改進 PostgreSQL 的查詢效能