From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Craig Ringer <craig@2ndquadrant.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: cedric@2ndquadrant.com, pgsql-performance@postgresql.org, Craig James <cjames@emolecules.com>
Cc: Kevin Grittner <kgrittn@mail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Claudio Freire <klaussfreire@gmail.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: David Greco <David_Greco@harte-hanks.com>
Subject: Re: Hints (was Poor performance using CTE)
Date: Wed, 28 Nov 2012 15:34:12 +1300
Message-ID: <50B57824.9040604@archidevsys.co.nz> (raw)
In-Reply-To: <50B57440.1040802@2ndquadrant.com>
References: <20121121134232.68590@gmx.com>
<CAFwQ8reE2vLynnE9zO_i7WhmktbiKaSYbBrCvJXCQ62feqBGZQ@mail.gmail.com>
<201211231105.56380.cedric@2ndquadrant.com>
<CAOR=d=1W8WqugTQBEe+kDUZbxBMXAfw413WzBBE1eOLD+A_mbw@mail.gmail.com>
<50B57440.1040802@2ndquadrant.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 28/11/12 15:17, Craig Ringer wrote:
> On 27/11/2012 3:42 PM, Scott Marlowe wrote:
>
>> Here here! PostgreSQL is well known for its extensibility and this is
>> the perfect place for hints.
> I agree with the sentiment and your concerns. However, this doesn't
> solve the CTE problem.
>
> Some people are relying on the planner's inability to push conditions
> into / pull conditions out of CTEs, and otherwise re-arrange them. If
> support for optimising into eligible CTEs (ie CTE terms that contain
> only SELECT or VALUES and call no VOLATILE functions) then these
> applications will potentially encounter serious performance regressions.
>
> Should this feature never be added to Pg, making it different and
> incompatible with other DBs that implement CTE optimisation, just
> because some people are using it for a hacky hint like OFFSET 0?
>
> Should these applications just be broken by the update, with people
> told to add `OFFSET 0` or load some not-yet-existing hints module
> after reporting the performance issue to the list?
>
> I don't think either of those are acceptable. Sooner or later
> somebody's going to want to add CTE optimisation, and I don't think
> that "you can't" or "great, we'll do it and break everything" are
> acceptable responses to any proposed patch someone might come up with
> to add that.
>
> A GUC might be OK, as apps can always SET it before problem queries or
> not-yet-ported code. It'd probably reduce the rate at which people
> fixed their code considerably, though, going by past experience with
> standard_conforming_strings, etc, but it'd work.
>
> --
> Craig Ringer
>
>
I think it would be best to be something in the SQL for SELECT, as:
1. One is more likely to find it by looking up the documentation for SELECT
2. It could allow selective application within a SELECT: one could have
several queries within the WITH clause: where all except one might
benefit for optimisation, and the exception might cause problems
I have suggested a couple possible syntax paterns, but there may well be
better alternative syntaxes.
Cheers,
Gavin
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: GavinFlower@archidevsys.co.nz, craig@2ndquadrant.com, scott.marlowe@gmail.com, cjames@emolecules.com, kgrittn@mail.com, tgl@sss.pgh.pa.us, klaussfreire@gmail.com, mmoncure@gmail.com, David_Greco@harte-hanks.com
Subject: Re: Hints (was Poor performance using CTE)
In-Reply-To: <50B57824.9040604@archidevsys.co.nz>
* 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