Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C6CC52E0066 for ; Thu, 13 Mar 2008 02:13:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37033-05 for ; Thu, 13 Mar 2008 02:13:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 0932B2E003F for ; Thu, 13 Mar 2008 02:13:26 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id m2D5DPhJ007557; Thu, 13 Mar 2008 01:13:25 -0400 (EDT) To: Craig Ringer cc: pgsql-performance@postgresql.org Subject: Re: Repeated execution of identical subqueries In-reply-to: <47D8A191.6000303@postnewspapers.com.au> References: <47D8A191.6000303@postnewspapers.com.au> Comments: In-reply-to Craig Ringer message dated "Thu, 13 Mar 2008 12:37:53 +0900" Date: Thu, 13 Mar 2008 01:13:24 -0400 Message-ID: <7556.1205385204@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/266 X-Sequence-Number: 29220 Craig Ringer writes: > Is there any way to get postgresql to detect such repeated query parts > and evaluate them only once? No, not at the moment. In principle the planner could look for such duplicates, but it'd be wasted cycles so much of the time that I'd be loath to do it. There is work afoot to implement the SQL:2003 "WITH" syntax, which I think would offer a syntactic solution to your problem. regards, tom lane