Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 33C7EB5DD59 for ; Tue, 25 Oct 2011 13:48:09 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 50985-07 for ; Tue, 25 Oct 2011 16:48:03 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from ns210619.ovh.net (ns210619.ovh.net [188.165.206.83]) by mail.postgresql.org (Postfix) with ESMTP id 21D04B5DD26 for ; Tue, 25 Oct 2011 13:47:57 -0300 (ADT) Received: from localhost (localhost.localdomain [127.0.0.1]) by ns210619.ovh.net (Postfix) with ESMTP id 12DBE3D1E00A; Tue, 25 Oct 2011 18:49:54 +0200 (CEST) X-Virus-Scanned: Debian amavisd-new at ns210619.ovh.net Received: from ns210619.ovh.net ([127.0.0.1]) by localhost (ns210619.ovh.net [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id mnLwF2wQsqJQ; Tue, 25 Oct 2011 18:49:52 +0200 (CEST) Received: from [192.168.1.4] (119.87.220.87.dynamic.jazztel.es [87.220.87.119]) by ns210619.ovh.net (Postfix) with ESMTPA id 356DA3D1E008; Tue, 25 Oct 2011 18:49:52 +0200 (CEST) Message-ID: <4EA6E839.6050804@linos.es> Date: Tue, 25 Oct 2011 18:47:53 +0200 From: Linos User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:7.0.1) Gecko/20111001 Thunderbird/7.0.1 MIME-Version: 1.0 To: Tom Lane CC: pgsql-performance@postgresql.org Subject: Re: CTE vs Subquery References: <4EA6E252.6030002@linos.es> <24672.1319561025@sss.pgh.pa.us> In-Reply-To: <24672.1319561025@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201110/211 X-Sequence-Number: 45433 El 25/10/11 18:43, Tom Lane escribió: > Linos writes: >> i am having any problems with performance of queries that uses CTE, can the >> join on a CTE use the index of the original table? > > CTEs act as optimization fences. This is a feature, not a bug. Use > them when you want to isolate the evaluation of a subquery. > > regards, tom lane > The truth it is that complex queries seems more readable using them (maybe a personal preference no doubt). Do have other popular databases the same behavior? SQL Server or Oracle for example? Regards, Miguel Ángel.