From: Bruce Momjian <bruce@momjian.us>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
Cc: Craig Ringer <craig@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: David Greco <David_Greco@harte-hanks.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: SOLVED - RE: Poor performance using CTE
Date: Fri, 23 Nov 2012 19:21:49 -0500
Message-ID: <20121124002149.GA1594@momjian.us> (raw)
In-Reply-To: <CAHyXU0w4OiA0Lg35Y0jJfp80GPAYjb6TivjYexMSuO-02aGN8Q@mail.gmail.com>
References: <187F6C10D2931A4386EE8E58E13857F61291D897@BY2PRD0811MB415.namprd08.prod.outlook.com>
<20462.1352910543@sss.pgh.pa.us>
<50A442A8.10803@2ndQuadrant.com>
<50A4497D.80902@dunslane.net>
<CAEYLb_XLgrFBMM5Ce9ZBoa0K+NY1hpFXyvXeaf6yWbHOjn3-3g@mail.gmail.com>
<CAHyXU0yPcfWQ58yU07vhit2PQ70kcxsDOr7QumZgvnKT=fcC-Q@mail.gmail.com>
<CAGTBQpY=7vJOCbypUs8kD0ii5rSAYOR5WpA8z1bMduYKphL+xA@mail.gmail.com>
<CAHyXU0w4OiA0Lg35Y0jJfp80GPAYjb6TivjYexMSuO-02aGN8Q@mail.gmail.com>
On Tue, Nov 20, 2012 at 02:24:01PM -0600, Merlin Moncure wrote:
> On Tue, Nov 20, 2012 at 1:53 PM, Jon Nelson <jnelson+pgsql@jamponi.net> wrote:
> > As can be seen by the current conversation, not everyone is convinced
> that CTEs ought to be an explicit optimization barrier
>
> On Tue, Nov 20, 2012 at 1:26 PM, Claudio Freire <klaussfreire@gmail.com> wrote:
> > It *could* just be a lack of imagination on my part. But if it were
> > not, then it'd be nice for it to be done automatically (since this
> > particular CTE behavior bites enough people already).
>
> Sure. I just find it personally hard to find a good demarcation line
> between A: "queries where pushing quals through are universally
> beneficial and wanted" and B: "queries where we are inserting an
> explicit materialization step to avoid planner issues", particularly
> where there is substantial overlap with between A and C: "queries that
> are written with a CTE and arguably shouldn't be".
>
> Put another way, I find CTE to express: 'this then that' where joins
> express 'this with that'. So current behavior is not surprising at
> all. All that said, there could be a narrow class of low hanging cases
> (such as the OP's) that could be sniped...I'm just skeptical.
Is thi
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
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: bruce@momjian.us, mmoncure@gmail.com, klaussfreire@gmail.com, jnelson+pgsql@jamponi.net, peter@2ndquadrant.com, andrew@dunslane.net, craig@2ndquadrant.com, tgl@sss.pgh.pa.us, David_Greco@harte-hanks.com
Subject: Re: SOLVED - RE: Poor performance using CTE
In-Reply-To: <20121124002149.GA1594@momjian.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