Thread: The performance issues caused by upgrading PostgreSQL to version 16.3.
Dear PostgreSQL Community, I hope this message finds you well. I am reaching out to seek your technical assistance regarding a performance issue weencountered after upgrading our PostgreSQL version from 12.19 to 16.3. We have noticed a significant performance problem with a specific SQL query on one of our application screens. Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executes injust a few seconds in pgAdmin. However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete. We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the parameters are set using NpgsqlCommand.Parameters.Add. The main table involved in this query contains approximately 800,000 records. We believe that the SQL statement itself does not have performance issues, but there may be problems related to how the SQLis executed in the application or how the parameters are set. However, we are unable to pinpoint the exact cause of the performance degradation. Could you please provide us with some possible reasons or suggestions for improvement? Your insights would be greatly appreciated. Thank you for your assistance, and I look forward to your response. Best regards, Ma
On Mon, 2025-02-17 at 07:55 +0000, 馬 騰飛 wrote: > I am reaching out to seek your technical assistance regarding a performance > issue we encountered after upgrading our PostgreSQL version from 12.19 to 16.3. > We have noticed a significant performance problem with a specific SQL query on > one of our application screens. > Interestingly, when we isolate the problematic SQL statement and replace its > parameters with actual values, it executes in just a few seconds in pgAdmin. > However, when we run the same SQL query through our application using Npgsql, > it takes over ten minutes to complete. I recommend using the auto_explain module to capture the plan of the statement when the application executes it. You best write the fast and the slow plan to the list, in plain text. Yours, Laurenz Albe -- *E-Mail Disclaimer* Der Inhalt dieser E-Mail ist ausschliesslich fuer den bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte, dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung oder Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen. *CONFIDENTIALITY NOTICE & DISCLAIMER *This message and any attachment are confidential and may be privileged or otherwise protected from disclosure and solely for the use of the person(s) or entity to whom it is intended. If you have received this message in error and are not the intended recipient, please notify the sender immediately and delete this message and any attachment from your system. If you are not the intended recipient, be advised that any use of this message is prohibited and may be unlawful, and you must not copy this message or attachment or disclose the contents to any other person.
On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <ma-tengfei@nec.cn> wrote:
Dear PostgreSQL Community,
I hope this message finds you well. I am reaching out to seek your technical assistance regarding a performance issue we encountered after upgrading our PostgreSQL version from 12.19 to 16.3.
We have noticed a significant performance problem with a specific SQL query on one of our application screens.
Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executes in just a few seconds in pgAdmin.
However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete.
We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the parameters are set using NpgsqlCommand.Parameters.Add.
The main table involved in this query contains approximately 800,000 records.
We believe that the SQL statement itself does not have performance issues, but there may be problems related to how the SQL is executed in the application or how the parameters are set.
However, we are unable to pinpoint the exact cause of the performance degradation.
Your situation sounds like something we encountered a few years ago in PG12. The solution was to add:
set plan_cache_mode = force_custom_plan
This is only for when the first five or six executions of a prepared statement run fast, and performance drops after that.
Test the query using PREPARE (https://www.postgresql.org/docs/16/sql-prepare.html) and ten different parameter sets, with and without "set plan_cache_mode = force_custom_plan".
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
Re: The performance issues caused by upgrading PostgreSQL to version 16.3.
From
Greg Sabino Mullane
Date:
On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <ma-tengfei@nec.cn> wrote:
Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executes in just a few seconds in pgAdmin.
However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete.
Another reason could be a poor type casting by your driver/middleware. Can you share the query that is problematic? (also, since you mentioned a version upgrade, also make sure you run ANALYZE; post-upgrade.)
Cheers,
Greg
post installing, did you run analyze verbose?
-Doron
-Doron
On Mon, Feb 17, 2025 at 4:23 PM Greg Sabino Mullane <htamfids@gmail.com> wrote:
On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <ma-tengfei@nec.cn> wrote:Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executes in just a few seconds in pgAdmin.
However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete.Another reason could be a poor type casting by your driver/middleware. Can you share the query that is problematic? (also, since you mentioned a version upgrade, also make sure you run ANALYZE; post-upgrade.)Cheers,Greg
---
Sent with Gmail for Sidekick Browser
Sent with Gmail for Sidekick Browser
On 2/16/25 23:55, 馬 騰飛 wrote: > Dear PostgreSQL Community, > > I hope this message finds you well. I am reaching out to seek your technical assistance regarding a performance issue weencountered after upgrading our PostgreSQL version from 12.19 to 16.3. > We have noticed a significant performance problem with a specific SQL query on one of our application screens. > Interestingly, when we isolate the problematic SQL statement and replace its parameters with actual values, it executesin just a few seconds in pgAdmin. > However, when we run the same SQL query through our application using Npgsql, it takes over ten minutes to complete. > We are using NpgsqlCommand.ExecuteReader to execute the SQL query, and the parameters are set using NpgsqlCommand.Parameters.Add. > The main table involved in this query contains approximately 800,000 records. > We believe that the SQL statement itself does not have performance issues, but there may be problems related to how theSQL is executed in the application or how the parameters are set. > However, we are unable to pinpoint the exact cause of the performance degradation. > Could you please provide us with some possible reasons or suggestions for improvement? Your insights would be greatly appreciated. This is going to need more information: 1) Was the upgrade done on the same machine or across machines? If across machines how are they different? 2) The table definition. 3) The query text. 4) The Npgsql code. > Thank you for your assistance, and I look forward to your response. > > Best regards, > Ma > > -- Adrian Klaver adrian.klaver@aklaver.com