public inbox for [email protected]
help / color / mirror / Atom feedFrom: Blake McBride <[email protected]>
To: Dave Cramer <[email protected]>
Cc: [email protected]
Subject: Re: Executing the same query multiple times gets slow
Date: Sat, 22 Jul 2023 16:41:26 -0500
Message-ID: <CABwHSOt2M12ZCZ53c83dJM+3W++h8ZH8pUf4wyEskOeKmJkaCw@mail.gmail.com> (raw)
In-Reply-To: <CADK3HHKvE2=V0GBgfQm5dJQqA66TcBtFZjsOVqnp-1C3LjadwQ@mail.gmail.com>
References: <CABwHSOurbLHgmvsEzTBRncvFoSt35RU1bs9jvF-287r6dDfD0Q@mail.gmail.com>
<CADK3HHJ3dxpsp76LJbMOfO9pU2618XfbKUX8OgKES09dMoaUVA@mail.gmail.com>
<CABwHSOvJ7+u8Zg29mRZQiooWwrjHBc5COP=EtVaf3z-Qnqt2Kg@mail.gmail.com>
<CADK3HH+EoympkvqEuT5tHVH6VTbPJ=xpc00dhDS_f_Af_PJ64A@mail.gmail.com>
<CABwHSOv3GJQu4_ahxFh3Q1DjYzMHwYd7ZBtSV3UzTXdkF+UTKw@mail.gmail.com>
<CADK3HHKvE2=V0GBgfQm5dJQqA66TcBtFZjsOVqnp-1C3LjadwQ@mail.gmail.com>
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?
Thanks.
Blake
On Sat, Jul 22, 2023 at 12:44 PM Dave Cramer <[email protected]>
wrote:
>
>
> On Sat, 22 Jul 2023 at 09:25, Blake McBride <[email protected]> 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 <[email protected]>
>> wrote:
>>
>>>
>>> On Fri, 21 Jul 2023 at 16:04, Blake McBride <[email protected]> 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 <[email protected]>
>>>> 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 <[email protected]>
>>>>> 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
>>>>>>
>>>>>>
view thread (8+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Executing the same query multiple times gets slow
In-Reply-To: <CABwHSOt2M12ZCZ53c83dJM+3W++h8ZH8pUf4wyEskOeKmJkaCw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox