Re: SQLFunctionCache and generic plans

po 3. 2. 2025 v 17:00 odesílatel Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> napsal:

> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> > Did you do some performance checks?
>
> This is a good question to ask ...
>
> > I tried some worst case
>
> > CREATE OR REPLACE FUNCTION fx(int)
> > RETURNS int AS $$
> > SELECT $1 + $1
> > $$ LANGUAGE SQL IMMUTABLE;
>
> ... but I don't think tests like this will give helpful answers.
> That function is simple enough to be inlined:
>
> regression=# explain verbose select fx(f1) from int4_tbl;
> QUERY PLAN
> ---------------------------------------------------------------
> Seq Scan on public.int4_tbl (cost=0.00..1.06 rows=5 width=4)
> Output: (f1 + f1)
> (2 rows)
>
> So functions.c shouldn't have any involvement at all in the
> actually-executed PERFORM expression, and whatever difference
> you measured must have been noise. (If the effect *is* real,
> we'd better find out why.)
>
> You need to test with a non-inline-able function. Looking
> at the inlining conditions in inline_function(), one simple
> hack is to make the function return SETOF. That'll only
> exercise the returns-set path in functions.c though, so it'd
> be advisable to check other inline-blocking conditions too.
>

I am sorry. I was wrong - I tested inlining on different case

(2025-02-03 17:24:25) postgres=# explain analyze verbose select fx(i) from
generate_series(1,10) g(i);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on pg_catalog.generate_series g (cost=0.00..0.13 rows=10
width=4) (actual time=0.016..0.018 rows=10 loops=1) │
│ Output: (i + i)
│
│ Function Call: generate_series(1, 10)
│
│ Planning:
│
│ Buffers: shared hit=11
│
│ Planning Time: 0.190 ms
│
│ Execution Time: 0.066 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(7 rows)

(2025-02-03 17:25:06) postgres=# explain analyze verbose select
fx((random()*100)::int) from generate_series(1,10) g(i);
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN
│
╞═════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡
│ Function Scan on pg_catalog.generate_series g (cost=0.00..2.68 rows=10
width=4) (actual time=0.104..0.169 rows=10 loops=1) │
│ Output: fx(((random() * '100'::double precision))::integer)
│
│ Function Call: generate_series(1, 10)
│
│ Planning Time: 0.054 ms
│
│ Execution Time: 0.182 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(5 rows)

I read https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions, and I
don't remember the rule `if an actual argument to the function call is a
volatile expression, then it must not be referenced in the body more than
once` well, so I didn't apply this rule correctly. I'll recheck this test.

Regards

Pavel

> regards, tom lane
>