Subject: [pgjdbc/pgjdbc] PR #3657: perf: cache composite and simple subqueries

From: "ilovethebeatles (@ilovethebeatles)" <[email-masked]>
Date: Tue, 10 Jun 2025 22:21:46 +0000

The driver now supports composite queries, which theoretically should execute far more efficiently. However, the current implementation treats every simple statement within a composite query as distinct, even when they’re identical. For example, in SQL like:  "select * from table where col = ?;  select * from table where col = ?; " the driver considers these two subqueries completely separate and rebuilds an execution plan for each.  

Queries are cached in a map called statementCache, where the key is the full SQL string. When a composite query runs, only the entire statement is cached—not its individual components. As a result, if a later composite query contains any of the same simple subqueries, their plans cannot be reused because they aren’t stored in statementCache; instead, they are regenerated from scratch. Since plan generation can be very time-consuming, this behavior significantly degrades performance in workloads with frequently repeated subqueries.  

In my optimization, I cache both composite queries and their constituent simple queries. Identical subqueries are now recognized by their SQL string, and their existing plans are reused, improving execution speed.  When caching, driver checks executeCount on full queries but does not update сount for subqueries. Therefore, if you use threshold=1 to cache subquery plans on first execution and enable reuse in subsequent composite queries. If your project needs to execute large composite parameterized queries with identical subqueries, enable threshold=1 to cache each subquery plan on first execution and maximize reuse.

Future work could include recalculating executeCount for subqueries and incorporating parameter data types into cache keys to further improve caching accuracy.

### All Submissions:

* [x] Have you followed the guidelines in our [Contributing](https://github.com/pgjdbc/pgjdbc/blob/master/CONTRIBUTING.md) document?
* [x] Have you checked to ensure there aren't other open [Pull Requests](../../pulls) for the same update/change?

<!-- You can erase any parts of this template not applicable to your Pull Request. -->

### New Feature Submissions:

1. [ ] Does your submission pass tests?
2. [ ] Does `./gradlew styleCheck` pass ?
3. [ ] Have you added your new test classes to an existing test suite in alphabetical order?

### Changes to Existing Features:

* [ ] Does this break existing behaviour? If so please explain.
* [x] Have you added an explanation of what your changes do and why you'd like us to include them?
* [x] Have you written new tests for your core changes, as applicable?
* [x] Have you successfully run tests with your changes locally?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 11 Jun 2025 18:45:18 +0000

cool, LGTM

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 11 Jun 2025 19:29:13 +0000

Could you please measure the performance before and after the change?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Thu, 12 Jun 2025 09:53:00 +0000

Please add a test that verifies a case when client uses different bind types for "the same" statement.

For example:

```java
String sql = "select * from inttable where a = ?;select * from inttable where a = ?;select * from inttable where a = ?";
...
ps.setInt(1, 42); ps.setString(2, "43"); ps.setDouble(3, 44.0);
```

The execution plan might depend on the result shape, so we can't easily reuse server-side handles by looking at the SQL alone.

We might be looking at https://github.com/pgjdbc/pgjdbc/issues/345

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Mon, 16 Jun 2025 19:31:31 +0000

(on benchmarks/src/jmh/java/org/postgresql/benchmark/statement/SelectBatch.java)

Have you considered using different benchmark methods instead?

---

From: "ilovethebeatles (@ilovethebeatles)" <[email-masked]>
Date: Sat, 21 Jun 2025 18:18:52 +0000

> We might be looking at #345

Added test for this case

---

From: "ilovethebeatles (@ilovethebeatles)" <[email-masked]>
Date: Sun, 22 Jun 2025 19:59:28 +0000

> Could you please measure the performance before and after the change?

Implemented benchmarks to measure throughput in the following cases:
1. Composite queries of different size, consisting of repeated subqueries, `prepareThreshold=1`  
   For k = 1…1000 (step 1), we build and execute a single SQL statement that repeats the subquery  
  "select val from bench_select where id = ?;"
k times (so first a 1-subquery statement, then 2-subquery, … up to 1000-subquery), cycling through these composite queries on one connection. With `threshold=1`, each subquery plan is cached on its first execution and then reused in all later composite queries.
2. Composite queries of different size, consisting of repeated subqueries, `prepareThreshold=5` (default)
Same loop, but with `threshold=5` (the default value for threshold). Subquery plans are only cached once the full composite statement has reached five executions, but because the 256-entry `statementCache` evicts those composite queries before they hit five runs, the subqueries never actually get cached. As a result, throughput stays the same as before.
3. Single parametrized `SELECT`
Just repeat the same simple `SELECT` under both thresholds to make sure the common case isn’t slower after the change.
Here are the results for the throughput in simple selects per second before and after my optimization:

| Scenario                          | Threshold      | Before, selects/s          | After , selects/s       |
|-----------------------------------|:--------------:|:----------------:|:----------------:|
| Composite batch-select loop       | 5 (default)    | 33 374 ± 383     | 33 622 ± 495     |
| Composite batch-select loop       | 1              | 29 355 ± 744     | 118 576 ± 450    |
| Single parametrized `SELECT`      | 5  (default)             | 13 643 ± 574     | 14 101 ± 68      |
| Single parametrized `SELECT`      | 1              | 14 143 ± 151     | 13 629 ± 496     |

- **Threshold=5**: no real change because subqueries never reach the threshold, so the behaviour doesn't change
- **Threshold=1**: 
    - ~3.5× speedup in comparison to `threshold=5`, showing the benefit of immediate subquery caching in large composite queries with frequently repeated subqueries
     - ~4× speedup over the stock driver, highlighting the impact of subquery deduplication.
- **Single-query**: performance within measurement noise, confirming no regression for the common case.

Further development might include tracking `executeCount` per subquery and adding parameter types to cache keys for better perfomance and accuracy.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 15:27:21 +0000

(on benchmarks/src/jmh/java/org/postgresql/benchmark/statement/SelectBatch.java:76)

Frankly speaking, I am afraid it is a suboptimal way of executing the queries.

The suggested SQL would have:
* 1-query statement
* 2-query statement
* 3-query statement
* ...
* 1000-query statement

That would consume n^2 memory.

The typical approach for these type of workloads is to use 2^N splits:

* 1-query statement
* 2-query statement
* 4-query statement
* 8-query statement
* 16-query statement
* 32-query statement
* 64-query statement
* 128-query statement
* 256-query statement
* 512-query statement
* 1024-query statement

Then you would have 11 queries that would be enough to cover any possible number of queries in a range of 1..2047.

For instance executing 356 would require 256+64+32+4=356.

WDYT?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 15:56:48 +0000

(on benchmarks/src/jmh/java/org/postgresql/benchmark/statement/SelectBatch.java:55)

```suggestion
    Properties props = new Properties();
    PGProperty.PREPARE_THRESHOLD.set(props, prepareThreshold);
    connection = TestUtil.openDB(props);
```

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 15:57:24 +0000

(on benchmarks/src/jmh/java/org/postgresql/benchmark/statement/SelectBatch.java:115)

try-with-resources?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 15:58:24 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java:1806)

Is there a reason to catch the exception? I guess test method could throw it.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 15:58:50 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java:1810)

I see the rest might be using `closeQuietly`, however, it would be better to go for try-with-resources in the new code

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 16:00:18 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java:1784)

Just wondering: do your production usages include executing statements of various shapes? For instance something like insert-select-insert, "select from a; select from b; select from c"?

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 16:02:58 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc2/PreparedStatementTest.java:1815)

Just wondering: is it covered with existing `BinaryMode=ON` test parameter?
If not, we should probably consider adding `prepareThreshold` variation on a global level rather than adding individual tests.

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 16:04:15 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java:234)

This looks like a leftover

```suggestion
```

---

From: "vlsi (@vlsi)" <[email-masked]>
Date: Wed, 02 Jul 2025 16:05:19 +0000

(on pgjdbc/src/test/java/org/postgresql/test/jdbc3/CompositeQueryParseTest.java:249)

`+e.getMessage()` rarely helps. Typically you want adding the relevant method arguments and/or local variables to make it easier for the end-user to analyze the failure