Improving Query Performance in PostgreSQL
Amy McCarty
Instructor
Order | Clause | Purpose |
---|---|---|
1 | FROM | provides directions to the table(s) |
2 | WHERE | filters or limits the records |
EXPLAIN
SELECT * FROM phones
Seq Scan on phones (cost = 0.00..22.7
,rows=1270
,width=36)
Query planner
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
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))
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[]))
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))
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[]))
Numeric advantages
Numeric advantages
Good | Better |
---|---|
Text | Numeric |
OR | IN, ARRAY |
Improving Query Performance in PostgreSQL