Re: For loop execution times in PostgreSQL 12 vs 15

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.

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