Re: Executing the same query multiple times gets slow

You can also set prepareThreshold to 0 and that will disable named
statements.

Otherwise my suggestion is to file an issue with pgsql-hackers.

Dave Cramer
www.postgres.rocks

On Sat, 22 Jul 2023 at 11:44, Dave Cramer <davecramer(at)postgres(dot)rocks> wrote:

>
>
> On Sat, 22 Jul 2023 at 09:25, Blake McBride <blake1024(at)gmail(dot)com> wrote:
>
>> 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.
>>
>
> So if you set plan_cache_mode to force_custom_mode does it remain fast ?
>
> If so, just set plan_cache_mode all the time.
>
> Dave
>
>>
>> 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
>>>>>>
>>>>>>