Performance issue with thousands of calls to procedures and functions?

From: "Daniel Westermann (DWE)" <daniel(dot)westermann(at)dbi-services(dot)com>
To: "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org>
Subject: Performance issue with thousands of calls to procedures and functions?
Date: 2021-07-30 07:12:17
Message-ID: ZR0P278MB0920DA81D97D5F30D4A46243D2EC9@ZR0P278MB0920.CHEP278.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

we have a customer which was migrated from Oracle to PostgreSQL 12.5 (I know, the latest version is 12.7). The migration included a lot of PL/SQL code. Attached a very simplified test case. As you can see there are thousands, even nested calls to procedures and functions. The test case does not even touch any relation, in reality these functions and procedures perform selects, insert and updates.

I've tested this on my local sandbox (Debian 11) and here are the results (three runs each):

Head:
Time: 97275.109 ms (01:37.275)
Time: 103241.352 ms (01:43.241)
Time: 104246.961 ms (01:44.247)

13.3:
Time: 122179.311 ms (02:02.179)
Time: 122622.859 ms (02:02.623)
Time: 125469.711 ms (02:05.470)

12.7:
Time: 182131.565 ms (03:02.132)
Time: 177393.980 ms (02:57.394)
Time: 177550.204 ms (02:57.550)

It seems there are some optimizations in head, but 13.3 and 12.7 are noticeable slower.

Question: Is it expected that this takes minutes sitting on the CPU or is there a performance issue? Doing the same in Oracle takes around 30 seconds. I am not saying that this implementation is brilliant, but for the moment it is like it is.

Thanks for any inputs
Regards
Daniel

Attachment Content-Type Size
_test_case.sql application/sql 5.4 KB

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Imre Samu 2021-07-30 08:01:42 Re: Performance issue with thousands of calls to procedures and functions?
Previous Message Justin Pryzby 2021-07-29 15:10:24 Re: Query performance !