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.
