Re: Performance difference when using views - Mailing list pgsql-performance

From Simon Riggs
Subject Re: Performance difference when using views
Date
Msg-id 1099348130.2709.105.camel@localhost.localdomain
Whole thread Raw
In response to Performance difference when using views  (Alvaro Nunes Melo <al_nunes@atua.com.br>)
List pgsql-performance
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


pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: Performance difference when using views
Next
From: Neil Conway
Date:
Subject: Re: Speeding up Gist Index creations