feat(sql): speed-up queries with multiple IN predicates on indexed columns by jerrinot · Pull Request #6112 · questdb/questdb

jerrinot · 2025-09-08T13:39:16Z

This speeds up PGWire queries with IN() predicates on multiple indexed columns.

Example:

[...]
where symA in (?, ?) and symB in (?, ?)
[...]

Details:
When both symA and symbB are indexed then the optimizer should plan index-scan on the column with higher cardinality. Since this column will likely result in a greater selectivity. However, before this PR the optimize would refuse to consider column cardinality in the presence of binding variables. Thus yielding a suboptimal plan.

This change it more relaxed. Reasoning: We cannot merge keys when binding variables are used. But if a column is 'new' (=different from the current key column) then we can still analyze it and possibly swap it for the existing key column. This does not involve any key merging => it's safe with binding variables.

TODO:

Proper testing, come up with scenarios where this could back-fire
User-centric PR description :)

…n indexes columns

coderabbitai · 2025-09-08T13:39:23Z

Important

Review skipped

Auto reviews are disabled on this repository.

Please check the settings in the CodeRabbit UI or the .coderabbit.yaml file in this repository. To trigger a single review, invoke the @coderabbitai review command.

You can disable this status message by setting the reviews.review_status to false in the CodeRabbit configuration file.

Walkthrough

Updates WhereClauseParser to conditionally allow merging list-of-values with bind variables based on whether a new column is being created. Adds a PG wire test verifying that query plans with bind variables favor the high-cardinality index regardless of predicate order.

Changes

Cohort / File(s)	Summary
Parser: in-list merge logic with bind variables `core/src/main/java/io/questdb/griffin/WhereClauseParser.java`	Adjusts conditions in analyzeListOfValues: merging is now gated by both key-value knowledge and whether it’s a new column. Bind-variable handling in the merge loop is conditioned on !newColumn. Adds clarifying comments.
Tests: PG wire plan with binding variables `core/src/test/java/io/questdb/test/cutlass/pgwire/PGJobContextTest.java`	Adds testPlanWithIndexAndBindingVariables: creates table with two symbol indexes, inserts rows, runs EXPLAIN with bind variables for IN lists, and asserts index scan on high-cardinality column for both predicate orders.

Sequence Diagram(s)

sequenceDiagram
  participant Query as Query (IN-list with binds)
  participant Parser as WhereClauseParser
  participant Analyzer as analyzeListOfValues
  participant Planner as Planner

  Query->>Parser: Parse WHERE with IN-list
  Parser->>Analyzer: analyzeListOfValues(nodes, newColumn)
  alt all key values known OR newColumn
    Analyzer->>Analyzer: Merge/validate values
    alt existing column AND bind variable present
      note over Analyzer: Skip merging when !newColumn and bind seen with prior values
    else new column or all known
      Analyzer->>Analyzer: Allow merge
    end
  else
    Analyzer->>Analyzer: Do not merge lists
  end
  Parser->>Planner: Emit analyzed predicates
  Planner->>Planner: Choose index based on cardinality
  Planner-->>Query: Plan (index scan on high-cardinality col)

Estimated code review effort

🎯 3 (Moderate) | ⏱️ ~25 minutes

Pre-merge checks and finishing touches

❌ Failed checks (1 warning)

Check name	Status	Explanation	Resolution
Docstring Coverage	⚠️ Warning	Docstring coverage is 25.00% which is insufficient. The required threshold is 80.00%.	You can run `@coderabbitai generate docstrings` to improve docstring coverage.

✅ Passed checks (2 passed)

Check name	Status	Explanation
Title Check	✅ Passed	The PR title succinctly identifies the feature and performance improvement by stating it speeds up SQL queries with multiple IN predicates on indexed columns, which directly matches the main change in the optimizer for PGWire queries.
Description Check	✅ Passed	The author’s description directly discusses speeding up PGWire queries with IN predicates on multiple indexed columns, explains the optimizer behavior with binding variables, and includes examples and testing notes, making it clearly related to the changeset.

Thanks for using CodeRabbit! It's free for OSS, and your support helps us grow. If you like it, consider giving us a shout-out.

❤️ Share

_{Comment @coderabbitai help to get the list of available commands and usage tips.}

jerrinot · 2025-09-08T13:56:48Z

@coderabbitai review

coderabbitai · 2025-09-08T13:56:55Z

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

puzpuzpuz · 2025-09-25T11:42:33Z

@coderabbitai review

coderabbitai · 2025-09-25T11:42:39Z

✅ Actions performed

Review triggered.

Note: CodeRabbit is an incremental review system and does not re-review already reviewed commits. This command is applicable only when automatic reviews are paused.

coderabbitai

Actionable comments posted: 2

🧹 Nitpick comments (1)

core/src/main/java/io/questdb/griffin/WhereClauseParser.java (1)
885-887: Use short-circuit boolean operator (&&) instead of bitwise &.

Replace & with && for clarity and to avoid unnecessary RHS evaluation.
-            if (!allKeyValuesAreKnown & !newColumn) {
+            if (!allKeyValuesAreKnown && !newColumn) {
                 return false;
             }

📜 Review details

Configuration used: Path: .coderabbit.yaml

Review profile: CHILL

Plan: Pro

📥 Commits

Reviewing files that changed from the base of the PR and between 7eb1a51 and 27bea2c.

📒 Files selected for processing (2)

core/src/main/java/io/questdb/griffin/WhereClauseParser.java (2 hunks)
core/src/test/java/io/questdb/test/cutlass/pgwire/PGJobContextTest.java (1 hunks)

⏰ Context from checks skipped due to timeout of 90000ms. You can increase the timeout in your CodeRabbit configuration to a maximum of 15 minutes (900000ms). (27)

GitHub Check: New pull request (Coverage Report Coverage Report)
GitHub Check: New pull request (SelfHosted Griffin And Fuzz tests on linux-arm64)
GitHub Check: New pull request (SelfHosted Griffin And Fuzz tests on linux-x64-zfs)
GitHub Check: New pull request (SelfHosted Other tests on linux-arm64)
GitHub Check: New pull request (SelfHosted Other tests on linux-x64-zfs)
GitHub Check: New pull request (Hosted Running tests on windows-other)
GitHub Check: New pull request (Hosted Running tests on windows-pgwire)
GitHub Check: New pull request (Hosted Running tests on windows-cairo)
GitHub Check: New pull request (Hosted Running tests on windows-fuzz2)
GitHub Check: New pull request (Hosted Running tests on windows-fuzz1)
GitHub Check: New pull request (Hosted Running tests on windows-griffin)
GitHub Check: New pull request (Hosted Running tests on mac-other)
GitHub Check: New pull request (Hosted Running tests on mac-pgwire)
GitHub Check: New pull request (Hosted Running tests on mac-cairo-fuzz)
GitHub Check: New pull request (SelfHosted Other tests Start X64Zfs Agent)
GitHub Check: New pull request (Hosted Running tests on mac-cairo)
GitHub Check: New pull request (SelfHosted Griffin And Fuzz tests Start ARM Agent)
GitHub Check: New pull request (SelfHosted Other tests Start ARM Agent)
GitHub Check: New pull request (Hosted Running tests on mac-griffin)
GitHub Check: New pull request (Rust Test and Lint on linux-jdk17)
GitHub Check: New pull request (SelfHosted Griffin And Fuzz tests Start X64Zfs Agent)
GitHub Check: New pull request (Hosted Running tests with cover on linux-other)
GitHub Check: New pull request (Hosted Running tests with cover on linux-pgwire)
GitHub Check: New pull request (Hosted Running tests with cover on linux-cairo)
GitHub Check: New pull request (Hosted Running tests with cover on linux-fuzz)
GitHub Check: New pull request (Hosted Running tests with cover on linux-griffin)
GitHub Check: New pull request (Check Changes Check changes)

core/src/main/java/io/questdb/griffin/WhereClauseParser.java

core/src/test/java/io/questdb/test/cutlass/pgwire/PGJobContextTest.java

core/src/main/java/io/questdb/griffin/WhereClauseParser.java

…tivty_with_binding_vars

…inding variable

glasstiger · 2025-10-02T09:08:29Z

[PR Coverage check]

😍 pass : 3 / 3 (100.00%)

file detail

	path	covered line	new line	coverage
🔵	io/questdb/griffin/WhereClauseParser.java	3	3	100.00%

feat(sql): improve execution plan when using multiple IN predicates o…

3c5d62b

…n indexes columns

jerrinot added the Performance Performance improvements label Sep 8, 2025

jerrinot marked this pull request as draft September 8, 2025 13:40

Merge branch 'master' into jh_indexes_in_selectivty_with_binding_vars

27bea2c

jerrinot marked this pull request as ready for review September 16, 2025 08:50

jerrinot changed the title ~~feat(sql): improve execution plan when using multiple IN predicates on indexed columns~~ feat(sql): speed-up queries with multiple IN predicates on indexed columns Sep 17, 2025

puzpuzpuz self-requested a review September 25, 2025 11:42

coderabbitai bot reviewed Sep 25, 2025

View reviewed changes

core/src/main/java/io/questdb/griffin/WhereClauseParser.java Show resolved Hide resolved

core/src/test/java/io/questdb/test/cutlass/pgwire/PGJobContextTest.java Show resolved Hide resolved

puzpuzpuz reviewed Sep 26, 2025

View reviewed changes

core/src/main/java/io/questdb/griffin/WhereClauseParser.java Show resolved Hide resolved

jerrinot added 5 commits October 1, 2025 09:52

Merge remote-tracking branch 'origin/master' into jh_indexes_in_selec…

4086325

…tivty_with_binding_vars

still to a high-cardinality column even when in() has just a single b…

09093b2

…inding variable

clarify comments

755eb5b

more comments

825efc4

d'oh

fbb74bf

puzpuzpuz self-requested a review October 1, 2025 15:55

puzpuzpuz approved these changes Oct 1, 2025

View reviewed changes

Merge branch 'master' into jh_indexes_in_selectivty_with_binding_vars

8c15d34

bluestreak01 merged commit 9ebcd3d into master Oct 2, 2025
35 checks passed

bluestreak01 deleted the jh_indexes_in_selectivty_with_binding_vars branch October 2, 2025 09:58

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(sql): speed-up queries with multiple IN predicates on indexed columns#6112

feat(sql): speed-up queries with multiple IN predicates on indexed columns#6112
bluestreak01 merged 8 commits intomasterfrom
jh_indexes_in_selectivty_with_binding_vars

jerrinot commented Sep 8, 2025 •

edited

Loading

Uh oh!

coderabbitai bot commented Sep 8, 2025 •

edited

Loading

Review skipped

Uh oh!

jerrinot commented Sep 8, 2025

Uh oh!

coderabbitai bot commented Sep 8, 2025

Uh oh!

puzpuzpuz commented Sep 25, 2025

Uh oh!

coderabbitai bot commented Sep 25, 2025

Uh oh!

coderabbitai bot left a comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

glasstiger commented Oct 2, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

jerrinot commented Sep 8, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

coderabbitai bot commented Sep 8, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Review skipped

Walkthrough

Changes

Sequence Diagram(s)

Estimated code review effort

Pre-merge checks and finishing touches

Uh oh!

jerrinot commented Sep 8, 2025

Uh oh!

coderabbitai bot commented Sep 8, 2025

Uh oh!

puzpuzpuz commented Sep 25, 2025

Uh oh!

coderabbitai bot commented Sep 25, 2025

Uh oh!

coderabbitai bot left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

glasstiger commented Oct 2, 2025

[PR Coverage check]

file detail

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

jerrinot commented Sep 8, 2025 •

edited

Loading

coderabbitai bot commented Sep 8, 2025 •

edited

Loading