From: Craig Ringer <craig@2ndQuadrant.com>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: Peter Geoghegan <peter@2ndquadrant.com>
Cc: Andrew Dunstan <andrew@dunslane.net>
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: Wed, 21 Nov 2012 07:33:51 +0800
Message-ID: <50AC135F.8030508@2ndQuadrant.com> (raw)
In-Reply-To: <CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@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>
<CAKuK5J1aR1_KvOr6-q_QLm_2PKw=W690Zbg=32WKMidpOLqWiA@mail.gmail.com>
On 11/21/2012 03:53 AM, Jon Nelson wrote:
> My perspective on this is that CTEs *should* be just like creating a
> temporary table and then joining to it, but without the
> materialization costs. In that respect, they seem like they should be
> like nifty VIEWs. If I wanted the behavior of materialization and then
> join, I'd do that explicitly with temporary tables, but using CTEs as
> an explicit optimization barrier feels like the explaining away
> surprising behavior.
I agree, especially since that barrier isn't specified as standard, so
we're using a standard feature with a subtle quirk as a
database-specific optimisation trick. A hint, as it were, like OFFSET 0.
*(Dons asbestos underwear an dives for cover)*
My big problem with the status quo is that it breaks queries from other
databases, like MS SQL server, where CTEs are optimised. I see this
periodically on Stack Overflow, with people asking variants of "Why
does PostgreSQL take 10,000 times longer to execute this query"? (not a
literal quote).
I really want to see this formalized and made explicit with `WITH
tablename AS MATERIALIZE (SELECT)` or similar.
Right now I often can't use CTEs to clean up hard-to-read queries
because of the optimisation barrier, so I have to create a temporary
view, temporary table, or use nested subqueries in FROM instead. Ugly.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
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: craig@2ndQuadrant.com, jnelson+pgsql@jamponi.net, mmoncure@gmail.com, peter@2ndquadrant.com, andrew@dunslane.net, tgl@sss.pgh.pa.us, David_Greco@harte-hanks.com
Subject: Re: SOLVED - RE: Poor performance using CTE
In-Reply-To: <50AC135F.8030508@2ndQuadrant.com>
* 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