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

Merlin Moncure <mmoncure(at)gmail(dot)com> writes:
> On Thu, May 9, 2013 at 10:15 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

Right, because then the perceived volatility of the expression isn't
increasing.

> 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).

foo('1923') will be folded to a constant at plan time.
to_date('1923','YYYY') will not be. That's the difference so far
as the planner is concerned. You can verify this with EXPLAIN VERBOSE.

The rule about not increasing the volatility of an expression is mainly
meant to prevent inlining a stable/immutable function that actually
contains a volatile-labeled function, because doing that would have
significantly greater consequences, eg not being able to use the
expression in an indexqual. But it's coded as "don't increase the
volatility at all". I'm not sure whether there would be severe
consequences if we allowed the immutable-to-stable case. It's at
least possible that we'd end up missing some constant-folding
opportunities, depending on the order of operations in the planner.

regards, tom lane