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
Improving Query Performance in PostgreSQL

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

Improving Query Performance in PostgreSQL

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

Improving Query Performance in PostgreSQL

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))
Improving Query Performance in PostgreSQL

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[]))
Improving Query Performance in PostgreSQL

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))
Improving Query Performance in PostgreSQL

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[]))
Improving Query Performance in PostgreSQL

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[]))
Improving Query Performance in PostgreSQL

Summarizing the best WHERE filters

 

Numeric advantages

  • Shorter length
  • Smaller storage
  • Speeds performance
Improving Query Performance in PostgreSQL

Summarizing the best WHERE filters

 

Numeric advantages

  • Shorter length
  • Smaller storage
  • Speeds performance

 

Good Better
Text Numeric
OR IN, ARRAY
Improving Query Performance in PostgreSQL

Let's practice!

Improving Query Performance in PostgreSQL

Preparing Video For Download...