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 11:44:09 -0600
Message-ID: <CADK3HHKvE2=V0GBgfQm5dJQqA66TcBtFZjsOVqnp-1C3LjadwQ@mail.gmail.com> (raw)
In-Reply-To: <CABwHSOv3GJQu4_ahxFh3Q1DjYzMHwYd7ZBtSV3UzTXdkF+UTKw@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>

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: <CADK3HHKvE2=V0GBgfQm5dJQqA66TcBtFZjsOVqnp-1C3LjadwQ@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