SET or STRICT modifiers on function affect planner row estimates

From: Michał Kłeczek <michal(at)kleczek(dot)org>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: SET or STRICT modifiers on function affect planner row estimates
Date: 2024-09-29 18:49:31
Message-ID: 5E915F62-D257-4823-896F-0E4C18D68FBA@kleczek.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Alexander Lakhin 2024-09-29 20:00:00 Re: msys inet_pton strangeness
Previous Message Tom Lane 2024-09-29 17:03:38 Re: pg_verifybackup: TAR format backup verification