Re: Executing the same query multiple times gets slow
| От | Dave Cramer |
|---|---|
| Тема | Re: Executing the same query multiple times gets slow |
| Дата | |
| Msg-id | CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Executing the same query multiple times gets slow (Blake McBride <blake1024@gmail.com>) |
| Список | pgsql-jdbc |
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
+Tom just for visibility.
Dave Cramer
www.postgres.rocks
On Thu, 20 Jul 2023 at 10:33, Blake McBride <blake1024@gmail.com> wrote:
Greetings,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 RAMPostgreSQL 15.1postgresql-42.5.4.jarI 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();where: PreparedStatement pstat;Sure appreciate any pointers!Thanks.Blake
В списке pgsql-jdbc по дате отправления: