public inbox for [email protected]
help / color / mirror / Atom feedFrom: Dave Cramer <[email protected]>
To: Blake McBride <[email protected]>
Cc: [email protected]
Subject: Re: Executing the same query multiple times gets slow
Date: Sun, 23 Jul 2023 07:47:39 -0600
Message-ID: <CADK3HH+Je7TAgLwOUvrLc9hsFhLHpJH+h32AO6mC4+BNJfecEA@mail.gmail.com> (raw)
In-Reply-To: <CABwHSOt2M12ZCZ53c83dJM+3W++h8ZH8pUf4wyEskOeKmJkaCw@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>
<CABwHSOt2M12ZCZ53c83dJM+3W++h8ZH8pUf4wyEskOeKmJkaCw@mail.gmail.com>
On Sat, 22 Jul 2023 at 15:41, Blake McBride <[email protected]> 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 <[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: <CADK3HH+Je7TAgLwOUvrLc9hsFhLHpJH+h32AO6mC4+BNJfecEA@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