This is somewhat of a known issue, although it should not get this bad.
After 5 iterations of the same query the driver will switch to a named statement. However this isn't the reason you are having problems. After 5 iterations of the same named prepared statement the backend will switch to a generic plan, which apparently is much slower. In newer versions of Postgres you can force it not to see PostgreSQL: Documentation: 15: 20.7. Query Planning So I think if you set plan_cache_mode to force_custom_mode you should be fine
I have a complex query that returns about 5,000 records and only a few columns. If I run it in psql repeatedly, it's always fast. If I run it through JDBC it runs fast at first but then it gets real slow (> 50 seconds). I can't understand why it would get slow after 30 runs of the exact same query.
I am running:
Linux / 64GB RAM
PostgreSQL 15.1
postgresql-42.5.4.jar
I am using a prepared statement but a new one each time even though it is the same query (there are reasons for this).
I found the line that's having the delay is: pstat.executeQuery();
