Re: For loop execution times in PostgreSQL 12 vs 15 - Mailing list pgsql-performance
| From | Pavel Stehule |
|---|---|
| Subject | Re: For loop execution times in PostgreSQL 12 vs 15 |
| Date | |
| Msg-id | CAFj8pRAMotEB_TEz4_xqQUjWgFXSGTCX_EjVmvYZNYw7nsJHsQ@mail.gmail.com Whole thread Raw |
| In response to | Re: For loop execution times in PostgreSQL 12 vs 15 (Andres Freund <andres@anarazel.de>) |
| List | pgsql-performance |
po 13. 2. 2023 v 22:22 odesílatel Andres Freund <andres@anarazel.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
pgsql-performance by date: