Re: Performance difference when using views

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