X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A935A3A4C02 for ; Mon, 15 Nov 2004 23:30:51 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 87612-07 for ; Mon, 15 Nov 2004 23:30:47 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 20A673A44DA for ; Mon, 15 Nov 2004 23:30:50 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAFNUfcb011693; Mon, 15 Nov 2004 18:30:42 -0500 (EST) To: Alexandre Leclerc Cc: PERFORM Subject: Re: Performance difference: SELECT from VIEW or not? In-reply-to: <1dc7f0e3041115151015e34130@mail.gmail.com> References: <1dc7f0e3041115151015e34130@mail.gmail.com> Comments: In-reply-to Alexandre Leclerc message dated "Mon, 15 Nov 2004 18:10:01 -0500" Date: Mon, 15 Nov 2004 18:30:41 -0500 Message-ID: <11692.1100561441@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/217 X-Sequence-Number: 9171 Alexandre Leclerc 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