Subject: [pgjdbc/pgjdbc] issue #3894: Performance regression caused by keeping QUERY_EXECUTE_AS_SIMPLE flag

From: "dreis2211 (@dreis2211)" <[email-masked]>
Date: Tue, 16 Dec 2025 16:18:48 +0000

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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Tue, 16 Dec 2025 18:51:38 +0000

@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/org/postgresql/core/v3/QueryExecutorImpl.java#L693 and check if it helps?

---

From: "dreis2211 (@dreis2211)" <[email-masked]>
Date: Tue, 16 Dec 2025 19:57:31 +0000

@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/org/postgresql/core/SetupQueryRunner.java#L50-L51 
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.

---

From: "dreis2211 (@dreis2211)" <[email-masked]>
Date: Wed, 17 Dec 2025 17:45:10 +0000

@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?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 17 Dec 2025 22:14:49 +0000

> [@vlsi](https://github.com/vlsi) Do you want me to contribute the suggested changes?
> 

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

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 18 Dec 2025 09:14:56 +0000

@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

---

From: "dreis2211 (@dreis2211)" <[email-masked]>
Date: Thu, 18 Dec 2025 09:24:31 +0000

@vlsi in that case I'll probably leave things to you, thank you