Обсуждение: For loop execution times in PostgreSQL 12 vs 15

Hi all,

I am running a simple test and am curious to know why a difference in execution times between PostgreSQL 12 vs PostgreSQL 15.

I have this function:

CREATE function test() returns int language plpgsql as $$
declare
      v_number bigint;
      v_multiplier float = 3.14159;
      loop_cnt bigint;
begin

      for loop_cnt in 1..1000000000
      loop
            v_number := 1000;
            v_number := v_number * v_multiplier;
      end loop;

      return 0;

end;$$;

I execute this in PostgreSQL 12:

PostgreSQL 15:

It is much faster in 15 than in 12, and while I love the performance improvement. I am curious to know the rationale behind this improvement on PostgreSQL 15.

The test result is from PostgreSQL on Windows but I observed the same behavior on Linux OS too.

Server Spec:

Intel i7-8650U CPU @1.90GHz 2.11GHz

RAM 16 GB

Windows 11 Enterprise

Thanks,
Adi

Please, don't send screenshots - we believe you :-)

Your code can be little bit faster if you use flag IMMUTABLE

There were more patches that reduced the overhead of expression's evaluation in PL/pgSQL.

History

https://github.com/postgres/postgres/commits/master/src/pl/plpgsql/src/pl_exec.c

Some interesting commits

https://github.com/postgres/postgres/commit/8f59f6b9c0376173a072e4fb7de1edd6a26e6b52

https://github.com/postgres/postgres/commit/fbc7a716084ebccd2a996cc415187c269ea54b3e

https://github.com/postgres/postgres/commit/73b06cf893c9d3bb38c11878a12cc29407e78b6c

Originally, PL/pgSQL was designed as glue of SQL and the expression evaluation was not too good. It was significantly slower in expression's evaluation than other interpreters like Perl or Python.

But lot of people uses PL/pgSQL for numeric calculations with PostGIS, so speed of expression's evaluation is more important than before, and after all optimizations, although the PL/pgSQL is still slower than generic interprets - still PL/pgSQL should be used mainly like glue of SQL,  the difference is significantly less - from 10x times slower to 2 slower. Still there is not any JIT - so the performance is almost good I think.

Regards

Pavel

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