From: Craig Ringer <craig@2ndquadrant.com>
To: 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: Tue, 27 Nov 2012 18:17:36 -0800
Message-ID: <50B57440.1040802@2ndquadrant.com> (raw)
In-Reply-To: <CAOR=d=1W8WqugTQBEe+kDUZbxBMXAfw413WzBBE1eOLD+A_mbw@mail.gmail.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>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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, 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: <50B57440.1040802@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