Re: For loop execution times in PostgreSQL 12 vs 15

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi


pá 10. 2. 2023 v 19:53 odesílatel Adithya Kumaranchath <akumaranchath@xxxxxxxx> napsal:
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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux