On 5/2/17 11:23 PM, Merlin Moncure wrote:
\On Tue, May 2, 2017 at 12:05 PM, Tomas Vondra
<tomas.von...@2ndquadrant.com> wrote:
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).
+1 down the line. More to the point, for several years now we've (or
at least I, but I'm not the only one) have been advocating for the
usage of CTE to avoid the undocumented and bizarre OFFSET 0 trick.
Jerking this out from users without giving a simple mechanic to get
the same behavior minus a major query rewrite is blatantly user
hostile. I can't believe we're even contemplating it. Also a GUC is
not a solution for pretty obvious reasons I think.
I'm not sure what you mean by "jerking this out from users". Isn't most
of this thread about how to allow CTE inlining without hurting users
unnecessarily?
I think we agree that:
* Just removing the optimization fence and telling users to use OFFSET 0
instead is a no-go, just like removing the fencing and not providing any
sensible replacement.
* GUC is not the solution.
Which leaves us with either WITH INLINE or WITH MATERIALIZE, or
something along those lines.
If we go with WITH INLINE then we're likely not solving anything,
because most people will simply use WITH just like now, and will be
subject to the fencing without realizing it.
Or we will choose WITH MATERIALIZE, and then the users aware of the
fencing (and using the CTEs for that purpose) will have to modify the
queries. But does adding MATERIALIZE quality as major query rewrite?
Perhaps combining this with a GUC would be a solution. I mean, a GUC
specifying the default behavior, and then INLINE / MATERIALIZE for
individual CTEs in a query?
If you have an application intentionally using CTEs as a fence, just do
ALTER DATABASE x SET enable_guc_fencing = on
and you don't have to rewrite the queries.
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
