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

Hi Michal,
It is difficult to understand the exact problem from your description.
Can you please provide EXPLAIN outputs showing the expected plan and
the unexpected plan; plans on the node where the query is run and
where the partitions are located.

On Mon, Sep 30, 2024 at 12:19 AM Michał Kłeczek <michal(at)kleczek(dot)org> wrote:
>
> Hi Hackers,
>
> I am not sure if this is a bug or I am missing something:
>
> There is a partitioned table with partitions being a mix of foreign and regular tables.
> I have a function:
>
> report(param text) RETURNS TABLE(…) STABLE LANGUAGE sql AS
> $$
> SELECT col1, expr1(col2), expr2(col2), sum(col3) FROM tbl GROUP BY col1, expr1(col2), expr2(col2)
> $$
>
> EXPLAIN SELECT * FROM report(‘xyz’);
>
> returns expected plan pushing down aggregate expression to remote server.
>
> When I add STRICT or SET search_path to the function definition, the plan is (as expected) a simple function scan.
> But - to my surprise - auto explain in the logs shows unexpected plan with all nodes scanning partitions having row estimates = 1
>
> Is it expected behavior?
>
> —
> Michal
>

--
Best Wishes,
Ashutosh Bapat