On Sunday, March 22, 2015, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "David G. Johnston" <david.g.johnston@gmail.com <javascript:;>> writes:
> > On Sunday, March 22, 2015, Paragon Corporation <lr@pcorp.us
> <javascript:;>> wrote:
> >> SELECT v[1] As v1, v[2] As v2, v[3] As v3
> >> FROM
> >> (SELECT dummy_notice(1,2,3) As v) As t;
>
> > I suspect Tom's optimization:
> >
> http://git.postgresql.org/pg/commitdiff/f4abd0241de20d5d6a79b84992b9e88603d44134
> > is flattening the array returning function into the select-list of the
> > outer query so it effectively reads:
> > Select Dummy_notice(1,2,3)[1], dummy_notice(1,2,3)[2], etc...
>
> Yeah. You could put "OFFSET 0" into the sub-select if you want to ensure
> it will not be flattened.
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.
>
> > The flatten would work if the result could be cached...it is defined to
> > be immutable...but that would not be reliable generally...
>
> Note that "immutable" effectively means "having no side-effects of
> interest", so emitting a NOTICE from such a function is really cheating.
> Another solution would be to mark the VOLATILE.
>
The notice is not important here and suggesting mis-defining an
immutable function as volatile is likewise an insufficient solution.
Presuming this optimization is the cause it should, at first glance, either
be fixed or reverted.
I'm still confused on when immutable functions are only invoked once for a
given set of arguments...since this seems like it should qualify. I
presume it is only a row-based optimization then?
David J.