Re: For loop execution times in PostgreSQL 12 vs 15

po 13. 2. 2023 v 22:22 odesílatel Andres Freund <andres(at)anarazel(dot)de> napsal:

> Hi,
>
> On 2023-02-10 20:45:39 +0100, Pavel Stehule wrote:
> > But for significant improvements it needs some form of JIT (Postgres has
> JIT
> > for SQL expressions, but it is not used for PLpgSQL expressions). On
> second
> > hand, PL/pgSQL is not designed (and usually) not used for extensive
> numeric
> > calculations like this. But if somebody try to enhance performance, (s)he
> > will be welcome every time (I think so there is some space for 2x better
> > performance - but it requires JIT).
>
> I think there's a *lot* of performance gain to be had before JIT is
> required. Or before JIT really can do a whole lot.
>
> We do a lot of work for each plpgsql statement / expr. Most of the time
> typically isn't spent actually evaluating expressions, but doing setup /
> invalidation work.
>

And it is the reason why I think JIT can help.

You repeatedly read and use switches based if the variable has fixed length
or if it is varlena, if it is native composite or plpgsql composite, every
time you check if target is mutable or not, every time you check if
expression type is the same as target type. The PL/pgSQL compiler is very
"lazy". Lots of checks are executed at runtime (or repeated). Another
question is the cost of v1 calling notation. These functions require some
environment, and preparing this environment is expensive. SQL executor has
a lot of parameters and setup is not cheap.

There are the same cases where expression: use buildin stable or immutable
functions, operators and types, and these types are immutable. Maybe it can
be extended with buffering for different search_paths, and then it cannot
be limited just for buildin's objects.

>
> E.g. here's a profile of the test() function from upthread:
>
> Overhead Command Shared Object Symbol
> + 17.31% postgres plpgsql.so [.] exec_stmts
> + 15.43% postgres postgres [.] ExecInterpExpr
> + 14.29% postgres plpgsql.so [.] exec_eval_expr
> + 11.79% postgres plpgsql.so [.] exec_assign_value
> + 7.06% postgres plpgsql.so [.] plpgsql_param_eval_var
> + 6.58% postgres plpgsql.so [.] exec_assign_expr
> + 4.82% postgres postgres [.] recomputeNamespacePath
> + 3.90% postgres postgres [.] CachedPlanIsSimplyValid
> + 3.45% postgres postgres [.] dtoi8
> + 3.02% postgres plpgsql.so [.] exec_stmt_fori
> + 2.88% postgres postgres [.]
> OverrideSearchPathMatchesCurrent
> + 2.76% postgres postgres [.] EnsurePortalSnapshotExists
> + 2.16% postgres postgres [.] float8mul
> + 1.62% postgres postgres [.] MemoryContextReset
>
> Some of this is a bit distorted due to inlining (e.g.
> exec_eval_simple_expr()
> is attributed to exec_eval_expr()).
>
>
> Most of the checks we do ought to be done once, at the start of plpgsql
> evaluation, rather than be done over and over, during evaluation.
>
> For things like simple exprs, we likely could gain a lot by pushing more of
> the work into ExecEvalExpr(), rather than calling ExecEvalExpr() multiple
> times.
>
> The memory layout of plpgsql statements should be improved, there's a lot
> of
> unnecessary indirection. That's what e.g. hurts exec_stmts() a lot.
>
> Greetings,
>
> Andres
>