On Wed, Jul 20, 2022 at 8:39 PM Tom Lane <tgl@xxxxxxxxxxxxx> 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
