Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKt1-0001Lu-V2 for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:43:24 +0000 Received: from outmail149113.authsmtp.com ([62.13.149.113]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TbKsz-00061Y-C7 for pgsql-performance@postgresql.org; Thu, 22 Nov 2012 00:43:23 +0000 Received: from mail-c209.authsmtp.com (mail-c209.authsmtp.com [62.13.128.209]) by punt5.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id qAM0h0aw085147; Thu, 22 Nov 2012 00:43:00 GMT Received: from ayaki.localdomain (124-148-90-153.dyn.iinet.net.au [124.148.90.153]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2) with ESMTP id qAM0gqo1098606 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 22 Nov 2012 00:42:55 GMT Message-ID: <50AD750B.8010108@2ndQuadrant.com> Date: Thu, 22 Nov 2012 08:42:51 +0800 From: Craig Ringer Organization: 2nd Quadrant User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121029 Thunderbird/16.0.2 MIME-Version: 1.0 To: Gavin Flower CC: Heikki Linnakangas , Andres Freund , Andrew Dunstan , Tom Lane , Claudio Freire , Merlin Moncure , David Greco , "pgsql-performance@postgresql.org" Subject: Re: Poor performance using CTE References: <50AC1472.3050607@2ndQuadrant.com> <18123.1353455585@sss.pgh.pa.us> <50ACD166.1000507@vmware.com> <50ACE970.7050701@dunslane.net> <3396.1353509947@sss.pgh.pa.us> <50ACF16C.2020902@dunslane.net> <20121121153209.GA1271@awork2.anarazel.de> <50ACF658.10902@archidevsys.co.nz> <50ACF991.3020603@vmware.com> <50AD2BD1.8070206@archidevsys.co.nz> <50AD6D16.9040608@2ndQuadrant.com> <50AD741E.1060100@archidevsys.co.nz> In-Reply-To: <50AD741E.1060100@archidevsys.co.nz> X-Enigmail-Version: 1.4.5 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Server-Quench: 8feed321-343d-11e2-9bd2-001f29070be2 X-AuthReport-Spam: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-AuthRoute: OCdxZQATClZeVg0b BQteCiN5VAwpPBRK HVkIKg5MJUcNSQVJ NksafxtFagdbY1ZH HGQLWlZEUF17XWF/ bgIfbAdZfEtKVxtj UFZIR1FNFgBqBAMA SF9vLxklNlpHeHd4 Y0ZkEHJYWkN6O04r FxsAFm8HeGRkazQC UUENdR5dIldIYx9F awZ9XXMKYTdSZDQC El17DAgcBxt4EgFg CjkKKkhaWUsREDkm QhkEFjhnAFYEQSp7 BCALQiIA X-Authentic-SMTP: 61633235383639.1012:706 X-AuthFastPath: 0 (Was 255) X-AuthSMTP-Origin: 124.148.90.153/2525 X-AuthVirus-Status: No virus detected - but ensure you scan with your own anti-virus system. X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201211/248 X-Sequence-Number: 48614 On 11/22/2012 08:38 AM, Gavin Flower wrote: > I suspect most people are blissfully unaware of CTE's being fenced, or > at least not really sure what it means. So I suspect NOT FENCE would > be the better default. It's also probably more standard, and a better fit with what other DBs do. Pg would still need to detect conditions like the use of functions with side effects or (obviously) INSERT/UPDATE/DELETE wCTEs and not push conditions down into them / pull conditions up from them, etc. That's how I read the standard, though; must have the same effect as if the queries were executed as written, so Pg is free to transform them so long as it doesn't change the results. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services