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

From Ashutosh Bapat
Subject Re: SET or STRICT modifiers on function affect planner row estimates
Date
Msg-id CAExHW5uGPF7Sa2xc8q8zAzn0+=QQKERdkePqWLD7VCE0++C6Ww@mail.gmail.com
Whole thread Raw
In response to SET or STRICT modifiers on function affect planner row estimates  (Michał Kłeczek <michal@kleczek.org>)
Responses Re: SET or STRICT modifiers on function affect planner row estimates
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



pgsql-hackers by date:

Previous
From: "Hayato Kuroda (Fujitsu)"
Date:
Subject: RE: long-standing data loss bug in initial sync of logical replication
Next
From: Andrew Dunstan
Date:
Subject: Re: msys inet_pton strangeness