Re: SQLFunctionCache and generic plans - Mailing list pgsql-hackers
| From | Pavel Stehule |
|---|---|
| Subject | Re: SQLFunctionCache and generic plans |
| Date | |
| Msg-id | CAFj8pRC_4gEfmDoB5KbacNDdt3e6Tnf1z1BFK=MVp3+2kcDVqw@mail.gmail.com Whole thread Raw |
| In response to | Re: SQLFunctionCache and generic plans (Tom Lane <tgl@sss.pgh.pa.us>) |
| List | pgsql-hackers |
po 3. 2. 2025 v 17:00 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.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)
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 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)
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 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
pgsql-hackers by date: