Re: Performance difference when using views

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux