Re: plan for function returning table combined with condition

On Wed, Jul 20, 2022 at 8:39 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> You want this SQL function to be inlined, but it isn't being.
> I think the reason is that (by default) it's VOLATILE, and
> inline_set_returning_function doesn't like that:
>
> * Forget it if the function is not SQL-language or has other
> showstopper
> * properties. In particular it mustn't be declared STRICT, since we
> * couldn't enforce that. It also mustn't be VOLATILE, because that is
> * supposed to cause it to be executed with its own snapshot, rather
> than
> * sharing the snapshot of the calling query. We also disallow
> returning
> * SETOF VOID, because inlining would result in exposing the actual
> result
> * of the function's last SELECT, which should not happen in that case.
>
> So try adding STABLE to the function definition.
>

Indeed, when I add STABLE to function, then planner uses index (same plan
as (B) in original post).
Thanks Tom.
, Thierry