Using stored procedures in Postgres (v16) is slower than standard functions. Is this expected?

[gary-ntnx]

Hi,

I am benchmarking Postgres 16 on Ubuntu Linux. I have a small, 300WH database with 100 vUsers and an using HammerDB v4.11 to drive the load from a remote machine. The DB is fully read-cached in RAM

When I create a DB and run the benchmark using diset tpcc pg_storedprocs true - the performance (NOPM) is about 10% lower than if I use diset tpcc pg_storedprocs false.

I was expecting pg_storedprocs true to be faster than default HammerDB which uses postgres "functions". Are these results expected? The below chart shows 5 runs (using dropdb/createdb/pg_restore) each time.

The configuration (300WH, 100vUsers - fully cached 32 CPU) uses ~85% of the available cores

[sm-shaw]

I will run some tests and provide a quantifiable update, but in general yes they are different and should not be expected to be identical.

Historically, when the HammerDB workload for PostgreSQL was first written there were no stored procedures, only functions (apart from EnterpriseDB oracle compatible) so naturally these came first - however functions only returned a function result and do not return data, so although the SQL is the same it is not passing back data via output parameters, although the difference in network traffic should not have a great impact.

Stored procedures on the other hand behave, as expected, more like stored procedures in other databases. Also, functions do not have transaction management for the function as a whole whereas stored procedures do, so this is likely to be the key difference. (although both should still pass the consistency checks).

Another difference is that a stored procedure call can't be a prepared statement whereas a function can - so if you use the XML connect pool option to define multiple connections for a workload then with PostgreSQL you can only use functions for this (which is one reason why we keep the functions option).

Another difference I am pretty sure (but will need to check) is that when using the HammerDB PostgreSQL metrics https://www.hammerdb.com/blog/uncategorized/how-to-analyze-postgresql-benchmark-performance-with-hammerdb/ with stored procedures you only see the stored procedure call, whereas with functions you see all the underlying SQL that is running.

Also just to note in my tests with PostgreSQL I often find the vaccumming impacts the performance over a test so there is some variability, although this should be the same for both functions and stored procedures.

I will provide some data from my tests when I have it.

[sm-shaw]

Example comparison at different releases (on single socket 56 cores). Functions generally perform better although at higher utilisation it can be marginal/lower for some releases. Version 17.0 performs the best from the releases tested. As noted above vacuuming can affect the result of an individual test.

For both functions and stored procedures, consistency checks run and passed after a number of runs as would be expected, so noting the differences above allows you to choose.

[sm-shaw]

And tested with HammerDB v4.12 which is staged and waiting for the TPC.

[gary-ntnx]

Thanks for the insight and data - I normally disable autovacuum for this reason. In fact sometimes I see the effect of vacuuming be very drastic (10X lower TPM) due to queries waiting on a lock.

I'll post a separate thread on this, since it's not really related to the function/stored-procedure discussion.

[sm-shaw]

We initially used to disable autovacuum when testing PostgreSQL, however if I recall correctly this resulted in transaction wraparound issues with longer running test sequences and therefore vacuuming while you go was the solution to ensure a valid sequence of runs even though this produced variation in individual runs. This was a few releases ago of PostgreSQL, so things may now be different, however the learning we had was vacuuming really was needed during these workloads with high transaction rates.