Re: Executing the same query multiple times gets slow

On Sat, 22 Jul 2023 at 15:41, Blake McBride <blake1024(at)gmail(dot)com> wrote:

> Thanks, Dave. I set it to force_custom_mode and the problem did go away.
> Great!
>
> Question though - what am I giving up by setting it to force_custom_mode?
>

When the planner switches to the generic plan it skips the planning phase.
This is supposed to save time, however in your case it doesn't.

Dave

>
> Thanks.
>
> Blake
>
> On Sat, Jul 22, 2023 at 12:44 PM 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
>>>>>>>
>>>>>>>