On Mon, Feb 17, 2025 at 2:55 AM 馬 騰飛 <ma-teng...@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!
