pgjdbc/pgjdbc GitHub issues and pull requests (mirror)
help / color / mirror / Atom feed[pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
7+ messages / 3 participants
[nested] [flat]
* [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-16 16:18 "dreis2211 (@dreis2211)" <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: dreis2211 (@dreis2211) @ 2025-12-16 16:18 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
Hi,
I found that after upgrading a Spring-Boot project the particular test suite and certain specific database heavy tests were 2-4 times slower than before. Running a single of those DB heavy tests via Gradle yields this:
**Before**
```
BUILD SUCCESSFUL in 3m 38s
```
**After**
```
BUILD SUCCESSFUL in 12m 57s
```
I could trace this back to an update from the postgresql driver from version [42.7.6](https://github.com/pgjdbc/pgjdbc/releases/tag/REL42.7.6) to [42.7.7](https://github.com/pgjdbc/pgjdbc/releases/tag/REL42.7.7).
As there are not many changes that could be responsible I looked at the following:
- https://github.com/pgjdbc/pgjdbc/commit/cb10dce086845b300f67125a7f33d59cc824c3d8
Particularly, the removal of the following case block
```
case EXTENDED:
return flags & ~QUERY_EXECUTE_AS_SIMPLE;
```
When reintroducing this and building a local snapshot, the tests run fast again. I'm not deep enough into the pgjdbc driver to make any call of why that is, but it has a relatively severe impact on us. Briefly looking into things, I wonder if more things than wanted reach "QueryExecutorImpl::sendSimpleQuery" now. At least logging reveals more activity there
```
....
FE=> SimpleQuery(query="SET application_name = 'PostgreSQL JDBC Driver'")
FE=> SimpleQuery(query="")
FE=> SimpleQuery(query="BEGIN")
FE=> SimpleQuery(query="BEGIN")
FE=> SimpleQuery(query="BEGIN")
FE=> SimpleQuery(query="BEGIN")
FE=> SimpleQuery(query="BEGIN")
FE=> SimpleQuery(query="BEGIN READ ONLY")
FE=> SimpleQuery(query="BEGIN")
.....
```
Basically every `BEGIN` (and that's in the thousands) etc. ends up there now, which seems to regress performance. But maybe it's also a completely different place that is impacted by keeping the QUERY_EXECUTE_AS_SIMPLE flag and thus reducing performance.
Unfortunately, I can't share the project at hand, but I hope you have enough information to investigate things. Let me know if I can help in any way.
Cheers,
Christoph
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-16 18:51 "vlsi (@vlsi)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: vlsi (@vlsi) @ 2025-12-16 18:51 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@dreis2211 , many thanks for the detailed analysis.
Would you please try commenting the line https://github.com/pgjdbc/pgjdbc/blob/eaf8063ef7d87c343c7cd97833b2fc263152d893/pgjdbc/src/main/java/... and check if it helps?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-16 19:57 "dreis2211 (@dreis2211)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: dreis2211 (@dreis2211) @ 2025-12-16 19:57 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi Surely. Doing so brings the particular test run down to ~5:30 minutes, so a little higher than the ~3:30 minutes, but there's always some fluctuation depending on how busy my machine is at the particular time of day. I would say removing the flag here as well
https://github.com/pgjdbc/pgjdbc/blob/eaf8063ef7d87c343c7cd97833b2fc263152d893/pgjdbc/src/main/java/...
has overall the same effect as bringing back the case block in the switch and sits currently at ~5 minutes at my machine.
EDIT: A morning session today with the suggested changes is at ~3:30 again, so as expected.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-17 17:45 "dreis2211 (@dreis2211)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: dreis2211 (@dreis2211) @ 2025-12-17 17:45 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi Do you want me to contribute the suggested changes?
Also - do you have an idea (or want to investigate) why running things with the `QUERY_EXECUTE_AS_SIMPLE` flag seems to be bad for performance?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-17 22:14 "davecramer (@davecramer)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: davecramer (@davecramer) @ 2025-12-17 22:14 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
> [@vlsi](https://github.com/vlsi) Do you want me to contribute the suggested changes?
>
> Also - do you have an idea (or want to investigate) why running things with the `QUERY_EXECUTE_AS_SIMPLE` flag seems to be bad for performance?
Yes, to contributing changes. Execute as simple is allegedly bad form. We are not supposed to mix simple and extended, however it has worked forever
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-18 09:14 "vlsi (@vlsi)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: vlsi (@vlsi) @ 2025-12-18 09:14 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@dreis2211 , there's an issue with just dropping "simple" flag from `BEGIN` statements as it might trigger a case like in https://github.com/pgjdbc/pgjdbc/issues/3107#issuecomment-3051715469
A trivial patch would probably be "refrain from execute as single when autosave=never", however, a proper fix would need more thought regarding "an extra sync message" or something like that.
> Do you want me to contribute the suggested changes?
I can cover the changes unless you are into analysis of how the flag plays with autosave and or issue #3107
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag
@ 2025-12-18 09:24 "dreis2211 (@dreis2211)" <[email protected]>
5 siblings, 0 replies; 7+ messages in thread
From: dreis2211 (@dreis2211) @ 2025-12-18 09:24 UTC (permalink / raw)
To: pgjdbc/pgjdbc <[email protected]>
@vlsi in that case I'll probably leave things to you, thank you
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-12-18 09:24 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-12-16 16:18 [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag "dreis2211 (@dreis2211)" <[email protected]>
2025-12-16 18:51 ` "vlsi (@vlsi)" <[email protected]>
2025-12-16 19:57 ` "dreis2211 (@dreis2211)" <[email protected]>
2025-12-17 17:45 ` "dreis2211 (@dreis2211)" <[email protected]>
2025-12-17 22:14 ` "davecramer (@davecramer)" <[email protected]>
2025-12-18 09:14 ` "vlsi (@vlsi)" <[email protected]>
2025-12-18 09:24 ` "dreis2211 (@dreis2211)" <[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