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

SET or STRICT modifiers on function affect planner row estimates

From

Michał Kłeczek

Date:

30 September, 00:49:31

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

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

From

Ashutosh Bapat

Date:

30 September, 18:14:11

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

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

From

Michał Kłeczek

Date:

01 October, 00:36:26

Hi,

Thanks for taking a look.

On 30 Sep 2024, at 14:14, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:

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.

The table structure is as follows:

CREATE TABLE tbl (…) PARTITION BY RANGE year(col02_date)

CREATE TABLE tbl_2015 PARTITION OF tbl FOR VALUES BETWEEN (2023) AND (2024) PARTITION BY HASH (col01_no)

… subsequent years

CREATE TABLE tbl_2021_32_9 PARTITION OF tbl_2021 FOR VALUES WITH (MODULUS 32 REMAINDER 9)

…

CREATE FOREIGN TABLE tbl_2022_16_9 PARTITION OF tbl_2022 FOR VALUES WITH (MODULUS 32 REMAINDER 9)

All tables are freshly ANALYZEd.

I have a function:

CREATE FUNCTION report(col01 text, year_from, month_from, year_to, month_to) RETURNS … LANGUAGE sql

SELECT

col01_no, year(col02_date), month(col02_date), sum(col03) FROM tbl WHERE col02_no = col02 AND (col02_date conditions) GROUP BY 1, 2, 3

EXPLAIN (…) SELECT * FROM report(…);

gives

Plan 1 (performs pushdown of aggregates):

Append (cost=9.33..76322501.41 rows=3 width=58) (actual time=5.051..6.414 rows=21 loops=1)

InitPlan 1 (returns $0)

-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

Output: '2021-01-01'::date

InitPlan 2 (returns $1)

-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=1)

Output: '2023-12-31'::date

-> GroupAggregate (cost=9.31..9.85 rows=1 width=58) (actual time=0.073..0.074 rows=0 loops=1)

Output: op.col01_no, (year(op.col02_date)), (month(op.col02_date)), sum(op.col03) FILTER (WHERE (op.debit_flag = '0'::bpchar)), sum(op.col03) FILTER (WHERE (op.debit_flag <> '0'::bpchar)), count(1)

Group Key: (year(op.col02_date)), (month(op.col02_date))

-> Sort (cost=9.31..9.32 rows=1 width=44) (actual time=0.072..0.073 rows=0 loops=1)

Output: (year(op.col02_date)), (month(op.col02_date)), op.col01_no, op.col03, op.debit_flag

Sort Key: (year(op.col02_date)), (month(op.col02_date))

Sort Method: quicksort Memory: 25kB

-> Index Only Scan using tbl_2021_32_9_universal_gist_idx_3a2df25af5bc48a on cbt.tbl_2021_32_9 op (cost=0.28..9.30 rows=1 width=44) (actual time=0.063..0.063 rows=0 loops=1)

Output: year(op.col02_date), month(op.col02_date), op.col01_no, op.col03, op.debit_flag

Index Cond: ((op.col01_no = '22109020660000000110831697'::text) AND (op.col02_date >= $0) AND (op.col02_date <= $1))

Filter: ((year(op.col02_date) >= 2021) AND (year(op.col02_date) <= 2023))

Heap Fetches: 0

-> Async Foreign Scan (cost=100.02..76322480.36 rows=1 width=58) (actual time=0.753..0.755 rows=11 loops=1)

Output: op_1.col01_no, (year(op_1.col02_date)), (month(op_1.col02_date)), (sum(op_1.col03) FILTER (WHERE (op_1.debit_flag = '0'::bpchar))), (sum(op_1.col03) FILTER (WHERE (op_1.debit_flag <> '0'::bpchar))), ((count(1))::double precision)

Relations: Aggregate on (cbt_c61d467d1b5fd1d218d9e6e7dd44a333.tbl_2022_16_9 op_1)

Remote SQL: SELECT col01_no, cbt.year(col02_date), cbt.month(col02_date), sum(col03) FILTER (WHERE (debit_flag = '0')), sum(col03) FILTER (WHERE (debit_flag <> '0')), count(1) FROM cbt.tbl_2022_16_9 WHERE ((cbt.year(col02_date) >= 2021)) AND ((cbt.year(col02_date) <= 2023)) AND ((col02_date >= $1::date)) AND ((col02_date <= $2::date)) AND ((col01_no = '22109020660000000110831697')) GROUP BY 1, 2, 3

-> GroupAggregate (cost=10.63..11.17 rows=1 width=58) (actual time=4.266..4.423 rows=10 loops=1)

Output: op_2.col01_no, (year(op_2.col02_date)), (month(op_2.col02_date)), sum(op_2.col03) FILTER (WHERE (op_2.debit_flag = '0'::bpchar)), sum(op_2.col03) FILTER (WHERE (op_2.debit_flag <> '0'::bpchar)), count(1)

Group Key: (year(op_2.col02_date)), (month(op_2.col02_date))

-> Sort (cost=10.63..10.64 rows=1 width=44) (actual time=4.238..4.273 rows=735 loops=1)

Output: (year(op_2.col02_date)), (month(op_2.col02_date)), op_2.col01_no, op_2.col03, op_2.debit_flag

Sort Key: (year(op_2.col02_date)), (month(op_2.col02_date))

Sort Method: quicksort Memory: 82kB

-> Index Only Scan using tbl_2023_128_9_universal_gist_idx_3a2df25af5bc48a on cbt.tbl_2023_128_9 op_2 (cost=0.54..10.62 rows=1 width=44) (actual time=0.295..4.059 rows=735 loops=1)

Output: year(op_2.col02_date), month(op_2.col02_date), op_2.col01_no, op_2.col03, op_2.debit_flag

Index Cond: ((op_2.col01_no = '22109020660000000110831697'::text) AND (op_2.col02_date >= $0) AND (op_2.col02_date <= $1))

Filter: ((year(op_2.col02_date) >= 2021) AND (year(op_2.col02_date) <= 2023))

Heap Fetches: 0

BUT after I perform

CREATE OR REPLACE report(…)

STRICT TO … AS … (same code)

The plan (as reported by auto_explain) changes to:

Plan 2 (no pushdown):

GroupAggregate (cost=1781983216.68..1781983324.62 rows=200 width=58) (actual time=16.065..16.432 rows=21 loops=1)

Output: op.col01_no, (year(op.col02_date)), (month(op.col02_date)), sum(op.col03) FILTER (WHERE (op.debit_flag = '0'::bpchar)), sum(op.col03) FILTER (WHERE (op.debit_flag <> '0'::bpchar)), count(1)

Group Key: (year(op.col02_date)), (month(op.col02_date))

InitPlan 1 (returns $0)

-> Result (cost=0.00..0.01 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=1)

Output: make_date($2, $3, 1)

InitPlan 2 (returns $1)

-> Result (cost=0.00..0.02 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=1)

Output: (((make_date($4, $5, 1) + '1 mon'::interval) - '1 day'::interval))::date

-> Sort (cost=1781983216.65..1781983217.33 rows=272 width=44) (actual time=16.041..16.117 rows=1564 loops=1)

Output: (year(op.col02_date)), (month(op.col02_date)), op.col01_no, op.col03, op.debit_flag

Sort Key: (year(op.col02_date)), (month(op.col02_date))

Sort Method: quicksort Memory: 171kB

-> Append (cost=0.55..1781983205.65 rows=272 width=44) (actual time=1.013..15.445 rows=1564 loops=1)

Subplans Removed: 269

-> Index Only Scan using accoper_2021_32_9_universal_gist_idx_3a2df25af5bc48a on cbt.accoper_2021_32_9 op_1 (cost=0.28..9.30 rows=1 width=44) (actual time=0.084..0.084 rows=0 loops=1)

Output: year(op_1.col02_date), month(op_1.col02_date), op_1.col01_no, op_1.col03, op_1.debit_flag

Index Cond: ((op_1.col01_no = $1) AND (op_1.col02_date >= $0) AND (op_1.col02_date <= $1))

Filter: ((year(op_1.col02_date) >= $2) AND (year(op_1.col02_date) <= $4))

Heap Fetches: 0

-> Async Foreign Scan on cbt_c61d467d1b5fd1d218d9e6e7dd44a333.accoper_2022_16_9 op_2 (cost=100.00..76322479.83 rows=1 width=44) (actual time=0.658..3.870 rows=829 loops=1)

Output: year(op_2.col02_date), month(op_2.col02_date), op_2.col01_no, op_2.col03, op_2.debit_flag

Remote SQL: SELECT col03, col02_date, debit_flag, col01_no FROM cbt.accoper_2022_16_9 WHERE ((col02_date >= $1::date)) AND ((col02_date <= $2::date)) AND ((col01_no = $3::text)) AND ((cbt.year(col02_date) >= $4::integer)) AND ((cbt.year(col02_date) <= $5::integer))

-> Index Only Scan using accoper_2023_128_9_universal_gist_idx_3a2df25af5bc48a on cbt.accoper_2023_128_9 op_3 (cost=0.54..10.62 rows=1 width=44) (actual time=0.361..4.043 rows=735 loops=1)

Output: year(op_3.col02_date), month(op_3.col02_date), op_3.col01_no, op_3.col03, op_3.debit_flag

Index Cond: ((op_3.col01_no = $1) AND (op_3.col02_date >= $0) AND (op_3.col02_date <= $1))

Filter: ((year(op_3.col02_date) >= $2) AND (year(op_3.col02_date) <= $4))

Heap Fetches: 0

I understand wrong rows estimates ( =1 ) are due to missing statistics on expressions year() and month().

But why plans are different?

BTW. I tried to add extended statistics on the above expressions but ANALYZE didn’t seem to update any values (as seen in pg_stat_ext) - ndistinct is NULL for example.

Thanks,

Michal

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

From

Tom Lane

Date:

01 October, 01:24:26

=?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org> writes:
>> On 30 Sep 2024, at 14:14, Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
>> 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.

> 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.
You have not mentioned your PG version, either.

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.

            regards, tom lane

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

From

Michał Kłeczek

Date:

01 October, 18:30:04

Hi Tom,

> On 30 Sep 2024, at 21:24, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> =?utf-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.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