Thread: Performance difference when using views
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@atua.com.br www.atua.com.br |
| UIN - 42722678 (54) 327-1044 |
+---------------------------------------------------+
Alvaro Nunes Melo <al_nunes@atua.com.br> writes:
> 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.
It's not the same query, because you are implicitly changing the order
of the LEFT JOINs when you group some of them into a subquery (view).
Join order is significant for outer joins ...
regards, tom lane
On Mon, 2004-11-01 at 21:40, Alvaro Nunes Melo wrote:
> 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;
>
Try this....
SELECT p.person_id, ci.city_id, ci.city_name, s.state_id,
s.state_acronym
FROM person p
LEFT OUTER JOIN ( 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 u ON ci.state_id = s.state_id )
USING (person_id)
WHERE a.adress_type = 2
AND p.person_id = 19257;
Which should return the same answer, and also hopefully the same plan.
--
Best Regards, Simon Riggs