Hi,
I have some views that are used to make some queries simplest. But when
I use them there is a performance loss, because the query don't use
indexes anymore. Below I'm sending the query with and without the view,
its execution times, explains and the view's body. I didn't understood
the why the performance is so different (20x in seconds, 1000x in page
reads) if the queries are semantically identical.
Shouldn't I use views in situations like this? Is there some way to use
the view and the indexes?
--------------
-- View body
--------------
CREATE VIEW vw_test AS
SELECT e.person_id, ci.city_id, ci.city_name, s.state_id,
s.state_acronym
FROM address a
LEFT OUTER JOIN zip zp ON a.zip_code_id = zp.zip_code_id
LEFT OUTER JOIN city ci ON ci.city_id = zp.city_id
LEFT OUTER JOIN state s ON ci.state_id = s.state_id
WHERE a.adress_type = 2;
---------------------
-- Without the view
---------------------
SELECT p.person_id, ci.city_id, ci.city_name, s.state_id,
s.state_acronym
FROM person p
LEFT OUTER JOIN address e USING (person_id)
LEFT OUTER JOIN zip zp ON a.zip_code_id = zp.zip_code_id
LEFT OUTER JOIN city ci ON ci.city_id = zp.city_id
LEFT OUTER JOIN state u ON ci.state_id = s.state_id
WHERE a.adress_type = 2
AND p.person_id = 19257;
person_id | city_id | city_name | state_id | state_acronym
-----------+-----------+-----------+----------+---------------
19257 | 70211 | JAGUARAO | 22 | RS
(1 record)
Time: 110,047 ms
QUERY PLAN
---------------------------------------------------------------------
Nested Loop Left Join (cost=0.00..20.04 rows=1 width=33)
Join Filter: ("outer".state_id = "inner".state_id)
-> Nested Loop Left Join (cost=0.00..18.43 rows=1 width=27)
-> Nested Loop Left Join (cost=0.00..13.87 rows=1 width=8)
-> Nested Loop (cost=0.00..10.75 rows=1 width=8)
-> Index Scan using pk_person on person p
(cost=0.00..5.41 rows=1 width=4)
Index Cond: (person_id = 19257)
-> Index Scan using un_address_adress_type on
address e (cost=0.00..5.33 rows=1 width=8)
Index Cond: (19257 = person_id)
Filter: (adress_type = 2)
-> Index Scan using pk_zip on zip zp (cost=0.00..3.11
rows=1 width=8)
Index Cond: ("outer".zip_code_id = zp.zip_code_id)
-> Index Scan using pk_city on city ci (cost=0.00..4.55
rows=1 width=23)
Index Cond: (ci.city_id = "outer".city_id)
-> Seq Scan on state u (cost=0.00..1.27 rows=27 width=10)
(15 records)
---------------------
-- With the view
---------------------
SELECT p.person_id, t.city_id, t.city_name, t.state_id, t.state_acronym
FROM person p
LEFT OUTER JOIN vw_test t USING (person_id)
WHERE p.person_id = 19257;
person_id | city_id | city_name | state_id | state_acronym
-----------+-----------+-----------+----------+--------------
19257 | 70211 | JAGUARAO | 22 | RS
(1 record)
Time: 1982,743 ms
QUERY PLAN
---------------------------------------------------------------------
Nested Loop Left Join (cost=10921.71..28015.63 rows=1 width=33)
Join Filter: ("outer".person_id = "inner".person_id)
-> Index Scan using pk_person on person p (cost=0.00..5.41 rows=1
width=4)
Index Cond: (person_id = 19257)
-> Hash Left Join (cost=10921.71..27799.55 rows=16854 width=33)
Hash Cond: ("outer".state_id = "inner".state_id)
-> Hash Left Join (cost=10920.38..27545.40 rows=16854
width=27)
Hash Cond: ("outer".city_id = "inner".city_id)
-> Hash Left Join (cost=10674.20..26688.88 rows=16854
width=8)
Hash Cond: ("outer".zip_code_id =
"inner".zip_code_id)
-> Seq Scan on address e (cost=0.00..1268.67
rows=16854 width=8)
Filter: (adress_type = 2)
-> Hash (cost=8188.36..8188.36 rows=387936
width=8)
-> Seq Scan on zip zp (cost=0.00..8188.36
rows=387936 width=8)
-> Hash (cost=164.94..164.94 rows=9694 width=23)
-> Seq Scan on city ci (cost=0.00..164.94
rows=9694 width=23)
-> Hash (cost=1.27..1.27 rows=27 width=10)
-> Seq Scan on state u (cost=0.00..1.27 rows=27
width=10)
(18 records)
Best regards,
--
+---------------------------------------------------+
| Alvaro Nunes Melo Atua Sistemas de Informacao |
| al_nunes@xxxxxxxxxxx www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
