From: Tom Lane <tgl@sss.pgh.pa.us>
To: Craig Ringer <craig@2ndQuadrant.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Poor performance using CTE
Date: Tue, 20 Nov 2012 18:53:05 -0500
Message-ID: <18123.1353455585@sss.pgh.pa.us> (raw)
In-Reply-To: <50AC1472.3050607@2ndQuadrant.com>
References: <187F6C10D2931A4386EE8E58E13857F612917154@BLUPRD0811MB413.namprd08.prod.outlook.com>
<CAHyXU0xeEVX2P=HaNxPiKayEhkWp3GobgAXCQPrfsqbawXVV-w@mail.gmail.com>
<CAGTBQpZ_Za-XSXvmPUx3F7wgeX=HB1xUpDsn+1DXY3a87YJLBQ@mail.gmail.com>
<CAHyXU0z4oUss50vhH_wGZ-HERQe9wCMSf1nMTGNddXeCoKvVVA@mail.gmail.com>
<CAGTBQpbf9bdJ3gjcYdw2FuPmN=dqx+wE7opsCp515cEndpGDYw@mail.gmail.com>
<50AC1472.3050607@2ndQuadrant.com>
Craig Ringer <craig@2ndQuadrant.com> writes:
> On 11/21/2012 12:06 AM, Claudio Freire wrote:
>> I meant for postgres to do automatically. Rewriting as a join wouldn't
>> work as an optimization fence the way we're used to, but pushing
>> constraints upwards can only help (especially if highly selective).
> Because people are now used to using CTEs as query hints, it'd probably
> cause performance regressions in working queries. Perhaps more
> importantly, Pg would have to prove that doing so didn't change queries
> that invoked functions with side-effects to avoid changing the results
> of currently valid queries.
We could trivially arrange to keep the current semantics if the CTE
query contains any volatile functions (or of course if it's
INSERT/UPDATE/DELETE). I think we'd also need to not optimize if
it's invoked from more than one place in the outer query.
I think the more interesting question is what cases wouldn't be covered
by such a rule. Typically you need to use OFFSET 0 in situations where
the planner has guessed wrong about costs or rowcounts, and I think
people are likely using WITH for that as well. Should we be telling
people that they ought to insert OFFSET 0 in WITH queries if they want
to be sure there's an optimization fence?
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, craig@2ndQuadrant.com, klaussfreire@gmail.com, mmoncure@gmail.com, David_Greco@harte-hanks.com
Subject: Re: Poor performance using CTE
In-Reply-To: <18123.1353455585@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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