public inbox for [email protected]help / color / mirror / Atom feed
The performance issues caused by upgrading PostgreSQL to version 16.3. 5+ messages / 5 participants [nested] [flat]
* The performance issues caused by upgrading PostgreSQL to version 16.3. @ 2025-02-17 07:55 =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= <[email protected]> 0 siblings, 3 replies; 5+ messages in thread From: =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= @ 2025-02-17 07:55 UTC (permalink / raw) To: [email protected] <[email protected]> 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. 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The performance issues caused by upgrading PostgreSQL to version 16.3. @ 2025-02-17 14:17 Ron Johnson <[email protected]> parent: =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Ron Johnson @ 2025-02-17 14:17 UTC (permalink / raw) To: pgsql-general On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <[email protected]> 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! ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The performance issues caused by upgrading PostgreSQL to version 16.3. @ 2025-02-17 14:22 Greg Sabino Mullane <[email protected]> parent: =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= <[email protected]> 2 siblings, 1 reply; 5+ messages in thread From: Greg Sabino Mullane @ 2025-02-17 14:22 UTC (permalink / raw) To: 馬 騰飛 <[email protected]>; +Cc: [email protected] <[email protected]> On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <[email protected]> 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 -- Crunchy Data - https://www.crunchydata.com Enterprise Postgres Software Products & Tech Support ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The performance issues caused by upgrading PostgreSQL to version 16.3. @ 2025-02-17 16:04 Doron Tsur <[email protected]> parent: Greg Sabino Mullane <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Doron Tsur @ 2025-02-17 16:04 UTC (permalink / raw) To: Greg Sabino Mullane <[email protected]>; +Cc: 馬 騰飛 <[email protected]>; [email protected] <[email protected]> post installing, did you run analyze verbose? -Doron On Mon, Feb 17, 2025 at 4:23 PM Greg Sabino Mullane <[email protected]> wrote: > On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <[email protected]> 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 > > -- > Crunchy Data - https://www.crunchydata.com > Enterprise Postgres Software Products & Tech Support > > -- --- Sent with Gmail for Sidekick Browser <https://join.meetsidekick.com/_qi4a5; ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The performance issues caused by upgrading PostgreSQL to version 16.3. @ 2025-02-17 16:10 Adrian Klaver <[email protected]> parent: =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= <[email protected]> 2 siblings, 0 replies; 5+ messages in thread From: Adrian Klaver @ 2025-02-17 16:10 UTC (permalink / raw) To: 馬 騰飛 <[email protected]>; [email protected] <[email protected]> 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 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. > 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 [email protected] ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-02-17 16:10 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-02-17 07:55 The performance issues caused by upgrading PostgreSQL to version 16.3. =?iso-2022-jp?B?GyRCR08hIUYtSHQbKEI=?= <[email protected]> 2025-02-17 14:17 ` Ron Johnson <[email protected]> 2025-02-17 14:22 ` Greg Sabino Mullane <[email protected]> 2025-02-17 16:04 ` Doron Tsur <[email protected]> 2025-02-17 16:10 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox