Re: Executing the same query multiple times gets slow

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
>>>>>
>>>>>