On 5/2/17 6:34 PM, David Fetter wrote:
On Tue, May 02, 2017 at 02:40:55PM +0200, Andreas Karlsson wrote:
On 05/02/2017 04:38 AM, Craig Ringer wrote:
On 1 May 2017 at 22:26, Andreas Karlsson <andr...@proxel.se> wrote:
>>
...
I see some alternatives, none of them perfect.
1. Just remove the optimization fence and let people add OFFSET 0 to their
queries if they want an optimization fence. This lets us keep pretending
that we do not have query hints (and therefore do not have to formalize any
syntax for them) while still allowing people to add optimization fences.
+1
I get that people with gigantic PostgreSQL installations with
stringent performance requirements sometimes need to do odd things to
squeeze out the last few percentage points of performance. As the
people (well, at least the people close to the ground) at these
organizations are fully aware, performance optimizations are extremely
volatile with respect to new versions of software, whether it's
PostgreSQL, Oracle, the Linux kernel, or what have you. They expect
this, and they have processes in place to handle it. If they don't,
it's pilot error.
We should not be penalizing all our other users to maintain the
fiction that people can treat performance optimizations as a "fire and
forget" matter.
Agreed.
2. Add a decorator for WITH (e.g. "WITH MATERIALIZED x (...") to add an
explicit optimization fence. This will for the first time add official
support for a query hint in the syntax which is a quite big precedent.
Yep. It's one we should think very carefully before we introduce.
I think it's a mistake to see this as an introduction of query hits.
Firstly, it's a question whether it qualifies as a hint. I wouldn't call
it a hint, but let's assume there is a definition of query hints that
includes WITH MATERIALIZED.
More importantly, however, this is not introducing anything new. It's
just a different name for the current "WITH" semantics, and you can
achieve the same behavior by "OFFSET 0". And people are already using
these as hints, so I fail to see how this introduces anything new.
In fact, if you see the optimization fence as an implicit query hint,
this actually *removes* a hint (although most users are unaware of that
behavior and use it unintentionally).
3. Add a new GUC which can enable and disable the optimization fence. This
is a very clumsy tool, but maybe good enough for some users and some people
here in this thread have complained about our similar GUCs.
Any GUC would be unable to distinguish one WITH clause from another.
The hammer would then be guaranteed to be too big for precisely the
cases where it's most needed.
If I could, I'd give -1 million to a GUC-based approach, as that would
make it entirely unusable in practice, I think.
Actually, I can give -1 million, so I'm giving it.
>>
4. Add some new more generic query hinting facility. This is a lot
of work and something which would be very hard to get consensus for.
Just the design of the thing would be the work of months at a minimum,
assuming we got to some consensus at all. Maybe it's worth doing.
While I came to conclusion that query hints may be quite useful in some
situations, I'm pretty sure this is not a battle you'd like to fight.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
