Re: SET or STRICT modifiers on function affect planner row estimates

Hi Tom,

> On 30 Sep 2024, at 21:24, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal(at)kleczek(dot)org> writes:
>>>
>
>> The table structure is as follows:
>
>> CREATE TABLE tbl (…) PARTITION BY RANGE year(col02_date)
>
> You're still expecting people to magically intuit what all those
> "..."s are. I could spend many minutes trying to reconstruct
> a runnable example from these fragments, and if it didn't behave
> as you say, it'd be wasted effort because I didn't guess right
> about some un-mentioned detail. Please provide a *self-contained*
> example if you want someone to poke into this in any detail.

Indeed - didn’t have time to provide a fully executable reproducer.
The issues are in a restricted environment and I cannot share any details.
Will try to provide one though.

OTOH the table structure itself didn’t seem important to me in this case
since the planner works fine in the first case. Anyway - the structure is:

CREATE TABLE tbl (col01_no text NOT NULL, col02_date date, col03 double precision)
PARTITION BY RANGE year(col02_date);

The functions year(date) and month(date) are shared in an extension common to all servers.

> You have not mentioned your PG version, either.

Indeed:
16.1

>
> My first guess would be that adding STRICT or adding a SET clause
> prevents function inlining, because it does. However, your Plan 2
> doesn't seem to involve a FunctionScan node, so either these plans
> aren't really what you say or there's something else going on.

Maybe this wasn’t stated clearly enough that the second plan was reported by auto_analyze in the logs.

That the function is not inlined is clear from the docs.
But what is not clear is why the query is planned differently in non-inlined functions.

The outer query is:

SELECT * FROM report(‘col01value’, 2021, 1, 2023, 12);

Kind regards

—
Michal