Re: SET or STRICT modifiers on function affect planner row estimates - Mailing list pgsql-hackers

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@kleczek.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