Re: Executing the same query multiple times gets slow

I have narrowed the problem down quite a bit. Here is what I found:

1. My original select was in error. I fixed it. Same problem.

2. If I do not use a prepared statement, it is fast all of the time. If I
use a prepared statement it runs fast most of the time and then after
repeated use starts getting really slow as I previously reported.

For me, in this instance, the solution is to not use a prepared statement.

In my opinion, this is a *major* flaw in either PostgreSQL or the JDBC
driver. My application has 10,000 Java classes and uses prepared
statements everywhere.

My solution in this instance will not work generally because there is too
much code to test and adjust. It is unreasonable for prepared statements
to work this significantly slower.

In my opinion, this is a huge problem and should be top priority.

Thanks!

Blake McBride

On Fri, Jul 21, 2023 at 5:10 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
wrote:

>
> On Fri, 21 Jul 2023 at 16:04, Blake McBride <blake1024(at)gmail(dot)com> wrote:
>
>> However, I see the problem through JDBC and not psql. Does that change
>> what you think?
>>
>>
> No, psql doesn't use prepared statements, If you wanted to see it in psql
> you would have to
> 1) create a prepared statement
> 2) set plan_cache_mode to force_generic_mode
> 3) execute the statement
>
> And you are right, sometimes the generic plan is the wrong choice, but it
> saves planning time.
>
> Dave
>
>> Thanks, Dave.
>>
>> Blake
>>
>>
>> On Fri, Jul 21, 2023 at 4:58 PM Dave Cramer <davecramer(at)postgres(dot)rocks>
>> wrote:
>>
>>> 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
>>> <https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE_MODE> 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(at)gmail(dot)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 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();
>>>>
>>>> where: PreparedStatement pstat;
>>>>
>>>> Sure appreciate any pointers!
>>>>
>>>> Thanks.
>>>>
>>>> Blake
>>>>
>>>>