Filtering in the WHERE clause

Improving Query Performance in PostgreSQL

Amy McCarty

Instructor

Limit the data

Order	Clause	Purpose
1	FROM	provides directions to the table(s)
2	WHERE	filters or limits the records

Occurs early
Fewer records

EXPLAIN

EXPLAIN
SELECT * FROM phones

Seq Scan on phones (cost = 0.00..22.7
                    ,rows=1270
                    ,width=36)

Number of execution steps

Query planner

Multiple chefs around a big cooking pot

EXPLAIN with WHERE

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)

Query planner

Multiple chefs around a big cooking pot

Good - Filtering for similar values with 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))

Better - Filtering for similar values with 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[]))

Good - Filtering for exact values with 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))

Better - Filtering for exact values with 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[]))

Best - Filtering for numbers

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[]))

Summarizing the best WHERE filters

Numeric advantages

Shorter length
Smaller storage
Speeds performance

Summarizing the best WHERE filters

Numeric advantages

Shorter length
Smaller storage
Speeds performance

Good	Better
Text	Numeric
OR	IN, ARRAY

Let's practice!

Improving Query Performance in PostgreSQL