pgjdbc/pgjdbc GitHub issues and pull requests (mirror)  

help / color / mirror / Atom feed

From: ilovethebeatles (@ilovethebeatles) <[email protected]>
To: pgjdbc/pgjdbc <[email protected]>
Subject: [pgjdbc/pgjdbc] PR #3657: perf: cache composite and simple subqueries
Date: Tue, 10 Jun 2025 22:21:46 +0000
Message-ID: <[email protected]> (raw)

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?

view thread (16+ 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: github://pgjdbc/pgjdbc
  Cc: [email protected], [email protected]
  Subject: Re: [pgjdbc/pgjdbc] PR #3657: perf: cache composite and simple subqueries
  In-Reply-To: <<[email protected]>>

* 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