Re: Does it make sense to break a large query into separate functions?

On Thu, May 9, 2013 at 10:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
>> right. note I think that (reduced invocation of immutable function) is
>> the correct behavior. If I say something is immutable, rightly or
>> wrongly, I am giving postgres a free hand to reduce function calls.
>> But (wandering off topic here), But I'm wondering why the stable
>> wrapper doesn't exhibit the same behavior.
>
> The reason it won't inline such a wrapper is that if it expands foo(x)
> to to_date(x, 'YYYY'), the latter expression will be considered stable,
> not immutable; so for example any subsequent opportunity to
> constant-fold it would not be taken.

But the stable decorated function *is* inlined. I read your reasoning
a couple of times above why decorating the function immutable (as
opposed to stable) defeats inlining, but I'm having trouble parsing
it. It's not clear to me why stable and immutable functions are
treated differently at all at plan time (regardless of decoration,
assuming we are not talking about volatile functions as non-volatile).

> It's conceivable that we could somehow decorate the parsetree so that
> the function call node for to_date() would still be considered immutable
> because it came out of inlining an immutable function. But that's not
> how things work now, and I'm dubious that it'd be worth the trouble.
> Really the right answer here is to label the wrapper with the same
> mutability level as its contents.

That's a fair point, but i'd state that to_date/YYYY *is* immutable in
this context and abstracting the functionality into a immutable
function seems reasonable (say, so that you could use the same
function in a functional index). Having to keep an alternate version
(marked stable) in order to preserve inlining seems ... awkward.

merlin