BUG #12869: PostGIS 2.2 can't compile against 9.5 dev branch

On Sunday, March 22, 2015, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
<javascript:_e(%7B%7D,'cvml','tgl(at)sss(dot)pgh(dot)pa(dot)us');>> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > This doesn't seem like a solution...if the flattened version of an all
> > constants invocation cannot be run only once where it could if it was not
> > flattened I would say the we've introduced a likely (and actual)
> > performance regression that punishes current valid and idiomatic code. I
> > haven't gone back and devised the entire reasoning for, and potential
> > benefit of, the flattening but both this and likely functions returning
> > composites are being negatively affected by this change.
>
> Well, it improves some queries and perhaps punishes others, but I should
> think the overall effect would generally be a win.

While I trust your judgement actual knowledge is nice. The fact that
PostGIS is raising the complaint should make us rethink those assumptions;
and nothing in their code or the minimal example strikes me as being
improper. That said, I am surprised you didn't recommend that the function
should be moved to a FROM clause - though I believe the code could still
optimize that way without the offset 0.

> Even in the case given
> here, it's far from clear that allowing flattening is a performance loss;
> the end result would have been a query containing only constants at run
> time, which in most scenarios would be a Good Thing.

The PostGIS example involves constructing geometries from a definition. I
suspect that is not cheap, which suggests that maybe we could recommend
increasing the cost of the function above the default and refusing to
flatten a function if the cost is sufficiently high?

>
> As for claiming that this is broken and should be reverted: nyet. In the
> first place, there is certainly no PG documentation anywhere that promises
> single evaluation of a function written in the manner shown here.

But what does the typical user expect? That should be the digested
standard: whether it is documented should be secondary. It is not
documented that the given query will be multiply evaluated either - and the
fact that the query, as written, only include the function call once would
suggest the user expect that it will only be evaluated once,

> We do, on the other hand, say that OFFSET 0 creates an optimization fence;
> so
> I see nothing wrong with my recommendation.

Given your dislike of planner hints I'm surprised you are so quick to
defend this solution. A hint is exactly what this is and requires the user
to know much more about the planner and postgresql internals than we shoud
be happy doing.

> In the second place, this was
> a HEAD-only change, and we certainly do not promise than the planner never
> changes behavior in major verions.

We do promise to not introduce regressions whenever possible. That
includes performance regressions.

So we don't know where the gain/loss boundary is, yet we know of at least
one common case that is being negatively affected, nor is the the suggested
hack appealing.

The appealing workaround, then, is to put the function call into a CTE and
hope we don't decide to act on the fact that it is not documented to be an
optimization fence.

I cannot help but feel there is a patch here that allows us to negate the
regression while keeping the benefits - but it's beyond my skills to prove
that let alone write it.

David J.