Re: SQLFunctionCache and generic plans

Hi

čt 30. 1. 2025 v 9:50 odesílatel Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru>
napsal:

> Alexander Pyhalov писал(а) 2025-01-29 17:35:
> > Tom Lane писал(а) 2025-01-17 21:27:
> >> Alexander Pyhalov <a(dot)pyhalov(at)postgrespro(dot)ru> writes:
> >>> I've rebased patch on master. Tests pass here.
> >>
> >> The cfbot still doesn't like it; my guess is that you built without
> >> --with-libxml and so didn't notice the effects on xml.out.
> >
> > Hi. Thank you for review.
> >
> > I've updated patch.
>
> Sorry, missed one local patch to fix memory bloat during replaning. Also
> fixed a compiler warning.
>

Did you do some performance checks?

I tried some worst case

CREATE OR REPLACE FUNCTION fx(int)
RETURNS int AS $$
SELECT $1 + $1
$$ LANGUAGE SQL IMMUTABLE;

CREATE OR REPLACE FUNCTION fx2(int)
RETURNS int AS $$
SELECT $1 * 2
$$ LANGUAGE SQL IMMUTABLE;

do $$
begin
for i in 1..1000000 loop
perform fx(i); -- or fx2
end loop;
end;
$$;
DO

The patched version reduces the difference between execution fx and fx2,
but patched version is about 10% slower than unpatched.

The overhead of plan cache looks significant for simple cases (and a lot of
SQL functions are very simple).

Regards

Pavel

>
> --
> Best regards,
> Alexander Pyhalov,
> Postgres Professional