Re: Performance difference: SELECT from VIEW or not? - Mailing list pgsql-performance

From Tom Lane
Subject Re: Performance difference: SELECT from VIEW or not?
Date
Msg-id 11692.1100561441@sss.pgh.pa.us
Whole thread Raw
In response to Performance difference: SELECT from VIEW or not?  (Alexandre Leclerc <alexandre.leclerc@gmail.com>)
List pgsql-performance
Alexandre Leclerc <alexandre.leclerc@gmail.com> writes:
> -- suppose this view (used many times):
> CREATE VIEW prod.alljobs_view AS
>   SELECT *
>   FROM prod.jobs
>     LEFT JOIN prod.jobs_products ON jobs.job_id = jobs_products.job_id;

> -- suppose this other query:
> CREATE VIEW prod.orders_jobs_view AS
>   SELECT job_id, order_id, product_code
>     FROM prod.alljobs_view
>       LEFT JOIN design.products ON alljobs_view.product_id =
> products.product_id;

> -- would this be more effective on database side than:?
> CREATE VIEW prod.orders_jobs_view AS
>   SELECT job_id, order_id, product_code
>     FROM prod.jobs
>       LEFT JOIN prod.jobs_products ON jobs.job_id = jobs_products.job_id
>       LEFT JOIN design.products ON jobs_products.product_id =
> products.product_id;

> Which is the best, or is there any difference?

For the specific case mentioned, there's not going to be any visible
difference (maybe a few more catalog lookups to expand two view
definitions instead of one).  However, it's real easy to shoot yourself
in the foot with views and outer joins.  Don't forget that the syntactic
ordering of outer joins is also a semantic and performance constraint.
(a left join b) left join c is different from a left join (b left join c)
and a view is basically a parenthesized subselect ...

            regards, tom lane

pgsql-performance by date:

Previous
From: Alexandre Leclerc
Date:
Subject: Performance difference: SELECT from VIEW or not?
Next
From: "dentfirst13@earthlink.net"
Date:
Subject: Re: Insertion puzzles