Re: [PERFORM] Performance difference when using views

2004-11-01 Thread Simon Riggs 
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


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [PERFORM] Performance difference when using views

2004-11-01 Thread Tom Lane 
Alvaro Nunes Melo <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[PERFORM] Performance difference when using views

2004-11-01 Thread Alvaro Nunes Melo 
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 MeloAtua Sistemas de Informacao |
| [EMAIL PROTECTED]www.atua.com.br   |
|UIN - 42722678(54) 327-1044|
+---+


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])