public inbox for [email protected]
help / color / mirror / Atom feedExecuting the same query multiple times gets slow
8+ messages / 3 participants
[nested] [flat]
* Executing the same query multiple times gets slow
@ 2023-07-20 16:33 Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Blake McBride @ 2023-07-20 16:33 UTC (permalink / raw)
To: [email protected]
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
@ 2023-07-21 21:58 ` Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: Dave Cramer @ 2023-07-21 21:58 UTC (permalink / raw)
To: Blake McBride <[email protected]>; +Cc: [email protected]; Tom Lane <[email protected]>
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
@ 2023-07-22 15:25 ` Blake McBride <[email protected]>
2023-07-22 17:44 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-24 05:53 ` Re: Executing the same query multiple times gets slow Laurenz Albe <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Blake McBride @ 2023-07-22 15:25 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: [email protected]
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.
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
>>>>
>>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
@ 2023-07-22 17:44 ` Dave Cramer <[email protected]>
2023-07-22 18:11 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 21:41 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
1 sibling, 2 replies; 8+ messages in thread
From: Dave Cramer @ 2023-07-22 17:44 UTC (permalink / raw)
To: Blake McBride <[email protected]>; +Cc: [email protected]
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
>>>>>
>>>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-22 17:44 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
@ 2023-07-22 18:11 ` Dave Cramer <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Dave Cramer @ 2023-07-22 18:11 UTC (permalink / raw)
To: Blake McBride <[email protected]>; +Cc: [email protected]
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
>>>>>>
>>>>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-22 17:44 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
@ 2023-07-22 21:41 ` Blake McBride <[email protected]>
2023-07-23 13:47 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Blake McBride @ 2023-07-22 21:41 UTC (permalink / raw)
To: Dave Cramer <[email protected]>; +Cc: [email protected]
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
>>>>>>
>>>>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-22 17:44 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 21:41 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
@ 2023-07-23 13:47 ` Dave Cramer <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: Dave Cramer @ 2023-07-23 13:47 UTC (permalink / raw)
To: Blake McBride <[email protected]>; +Cc: [email protected]
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
>>>>>>>
>>>>>>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Executing the same query multiple times gets slow
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Re: Executing the same query multiple times gets slow Dave Cramer <[email protected]>
2023-07-22 15:25 ` Re: Executing the same query multiple times gets slow Blake McBride <[email protected]>
@ 2023-07-24 05:53 ` Laurenz Albe <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Laurenz Albe @ 2023-07-24 05:53 UTC (permalink / raw)
To: Blake McBride <[email protected]>; Dave Cramer <[email protected]>; +Cc: [email protected]
On Sat, 2023-07-22 at 10:25 -0500, Blake McBride 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.
>
> 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.
There is an alternative to blindly disabling PostgreSQL's use of a generic plan:
you could examine the generic plan and try to improve it.
In PostgreSQL v16, you can use
EXPLAIN (GENERIC_PLAN) SELECT /* query with $1, $2 etc. as parameters */
to get the generic plan. With older versions, you can (using the appropriate
number and type for the parameters)
PREPARE stmt(integer,text) AS SELECT /* query with $1, $2 etc. as parameters */
EXPLAIN (ANALYZE, BUFFERS) stmt(1, 'something');
Repeat the EXECUTE at least 5 times, until PostgreSQL switches to the generic plan.
Then you can analyze why the generic plan is so slow. It is often possible to
improve PostgreSQL's estimates to either make it pick a better generic plan or
to make it *not* switch to a generic plan by itself. Ask for help if you cannot
do that alone.
That way, you don't have to disable generic plans.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-07-24 05:53 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-20 16:33 Executing the same query multiple times gets slow Blake McBride <[email protected]>
2023-07-21 21:58 ` Dave Cramer <[email protected]>
2023-07-22 15:25 ` Blake McBride <[email protected]>
2023-07-22 17:44 ` Dave Cramer <[email protected]>
2023-07-22 18:11 ` Dave Cramer <[email protected]>
2023-07-22 21:41 ` Blake McBride <[email protected]>
2023-07-23 13:47 ` Dave Cramer <[email protected]>
2023-07-24 05:53 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox