Re: Executing the same query multiple times gets slow

public inbox for [email protected]  
help / color / mirror / Atom feed

From: Dave Cramer <[email protected]>
To: Blake McBride <[email protected]>
Cc: [email protected]
Subject: Re: Executing the same query multiple times gets slow
Date: Sat, 22 Jul 2023 12:11:29 -0600
Message-ID: <CADK3HHLm8jmFqMWGxhh_=_pWHo+gDyLZWdKgV5pDSfS1wAAPiQ@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>

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 <[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: <CADK3HHLm8jmFqMWGxhh_=_pWHo+gDyLZWdKgV5pDSfS1wAAPiQ@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