pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance difference: SELECT from VIEW or not?
2+ messages / 2 participants
[nested] [flat]

* Performance difference: SELECT from VIEW or not?
@ 2004-11-15 23:10  Alexandre Leclerc <alexandre.leclerc@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Alexandre Leclerc @ 2004-11-15 23:10 UTC (permalink / raw)
  To: pgsql-performance

I'm just currious about which is the best, if I have many query based
on the first one:

-- 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? (I can't test it
myself, I have too few data).

Regards.

-- 
Alexandre Leclerc



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Performance difference: SELECT from VIEW or not?
@ 2004-11-15 23:30  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Alexandre Leclerc <alexandre.leclerc@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2004-11-15 23:30 UTC (permalink / raw)
  To: Alexandre Leclerc <alexandre.leclerc@gmail.com>; +Cc: 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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-11-15 23:30 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-11-15 23:10 Performance difference: SELECT from VIEW or not? Alexandre Leclerc <alexandre.leclerc@gmail.com>
2004-11-15 23:30 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox