perf(sql): parallel execution of ORDER BY + LIMIT queries (up to 10x speed-up) by puzpuzpuz · Pull Request #5957 · questdb/questdb

puzpuzpuz · 2025-07-18T10:07:55Z

Adds new factory for parallel execution of SQL queries with ORDER BY and LIMIT combination (a.k.a. top K), e.g.

SELECT * FROM trades
WHERE timestamp in today()
ORDER BY price * amount DESC
LIMIT 3;

The parallel execution can be disabled by setting the new cairo.sql.parallel.topk.enabled=false config property value.

Also fixes query cache clean-up on HTTP and PGWire connection close. Freeing all factories in the global query cache when a connection is closed was hurting cache efficiency in case of short-lived connections.

Design

Instead of gathering top K records on a single threads, the new factory calculates top K records on each worker thread. Once that's done, the gathered top K tree chains are merged into a single one which is used to return the result set.

So, the pipeline is the following:

Publish and gather top K reduce tasks (parallel)
Merge top K tree chains calculated by each worker into a single one (single-threaded)

Benchmarks

Results for ClickBench Q25 on my machine:

-- patch: 76ms
-- master: 360ms
SELECT SearchPhrase FROM hits WHERE SearchPhrase IS NOT NULL ORDER BY SearchPhrase LIMIT 10;

bluestreak01 · 2025-07-19T12:08:30Z

great job @puzpuzpuz ! what is your machine?

puzpuzpuz · 2025-07-19T12:46:27Z

great job @puzpuzpuz ! what is your machine?

Ryzen 7900x (12c/24t), 64GB RAM, Ubuntu 22.04.

…top_k

coderabbitai · 2025-08-06T12:14:24Z

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.

✨ Finishing Touches

🧪 Generate unit tests

Create PR with unit tests
Post copyable unit tests in a comment
Commit unit tests in branch puzpuzpuz_parallel_top_k

🪧 Tips

Chat

There are 3 ways to chat with CodeRabbit:

‼️ IMPORTANT
Auto-reply has been disabled for this repository in the CodeRabbit settings. The CodeRabbit bot will not respond to your replies unless it is explicitly tagged.

Files and specific lines of code (under the "Files changed" tab): Tag @coderabbitai in a new review comment at the desired location with your query. Examples:
- @coderabbitai explain this code block.
PR comments: Tag @coderabbitai in a new PR comment to ask questions about the PR branch. For the best results, please provide a very specific query, as very limited context is provided in this mode. Examples:
- @coderabbitai gather interesting stats about this repository and render them as a table. Additionally, render a pie chart showing the language distribution in the codebase.
- @coderabbitai read src/utils.ts and explain its main purpose.
- @coderabbitai read the files in the src/scheduler package and generate a class diagram using mermaid and a README in the markdown format.

Support

Need help? Create a ticket on our support page for assistance with any issues or questions.

CodeRabbit Commands (Invoked using PR comments)

@coderabbitai pause to pause the reviews on a PR.
@coderabbitai resume to resume the paused reviews.
@coderabbitai review to trigger an incremental review. This is useful when automatic reviews are disabled for the repository.
@coderabbitai full review to do a full review from scratch and review all the files again.
@coderabbitai summary to regenerate the summary of the PR.
@coderabbitai generate docstrings to generate docstrings for this PR.
@coderabbitai generate sequence diagram to generate a sequence diagram of the changes in this PR.
@coderabbitai generate unit tests to generate unit tests for this PR.
@coderabbitai resolve resolve all the CodeRabbit review comments.
@coderabbitai configuration to show the current CodeRabbit configuration for the repository.
@coderabbitai help to get help.

Other keywords and placeholders

Add @coderabbitai ignore anywhere in the PR description to prevent this PR from being reviewed.
Add @coderabbitai summary to generate the high-level summary at a specific location in the PR description.
Add @coderabbitai anywhere in the PR title to generate the title automatically.

Documentation and Community

Visit our Documentation for detailed information on how to use CodeRabbit.
Join our Discord Community to get help, request features, and share feedback.
Follow us on X/Twitter for updates and announcements.

core/src/main/java/io/questdb/griffin/engine/table/AsyncTopKRecordCursor.java

bluestreak01 · 2025-08-06T12:45:56Z

the speedup i have is from this:

to this:

core/src/main/java/io/questdb/cairo/sql/async/PageFrameReduceTask.java

core/src/test/java/io/questdb/test/griffin/OrderByTimeoutTest.java

core/src/main/java/io/questdb/griffin/engine/table/AsyncTopKRecordCursorFactory.java

…top_k

glasstiger · 2025-08-06T17:41:39Z

[PR Coverage check]

😍 pass : 428 / 467 (91.65%)

file detail

	path	covered line	new line	coverage
🔵	io/questdb/griffin/engine/table/AsyncGroupByAtom.java	0	2	00.00%
🔵	io/questdb/cairo/wal/WalApplySqlExecutionContext.java	0	1	00.00%
🔵	io/questdb/griffin/engine/table/AsyncGroupByRecordCursorFactory.java	2	4	50.00%
🔵	io/questdb/griffin/engine/orderby/RecordComparatorCompiler.java	5	8	62.50%
🔵	io/questdb/cairo/sql/async/PageFrameSequence.java	11	14	78.57%
🔵	io/questdb/griffin/engine/functions/window/RankFunctionFactory.java	33	38	86.84%
🔵	io/questdb/griffin/engine/table/AsyncTopKRecordCursor.java	92	104	88.46%
🔵	io/questdb/griffin/engine/table/AsyncTopKRecordCursorFactory.java	79	84	94.05%
🔵	io/questdb/griffin/engine/table/AsyncFilterUtils.java	22	23	95.65%
🔵	io/questdb/griffin/engine/table/AsyncTopKAtom.java	104	109	95.41%
🔵	io/questdb/griffin/engine/table/AsyncGroupByNotKeyedRecordCursorFactory.java	2	2	100.00%
🔵	io/questdb/PropServerConfiguration.java	2	2	100.00%
🔵	io/questdb/griffin/engine/table/AsyncFilteredRecordCursor.java	9	9	100.00%
🔵	io/questdb/griffin/engine/orderby/LongTopKRecordCursorFactory.java	1	1	100.00%
🔵	io/questdb/griffin/engine/table/FwdTableReaderPageFrameCursor.java	1	1	100.00%
🔵	io/questdb/cairo/sql/PageFrameMemoryRecord.java	1	1	100.00%
🔵	io/questdb/cairo/sql/async/PageFrameReduceJob.java	2	2	100.00%
🔵	io/questdb/cairo/DefaultCairoConfiguration.java	1	1	100.00%
🔵	io/questdb/griffin/SqlExecutionContextImpl.java	4	4	100.00%
🔵	io/questdb/PropertyKey.java	1	1	100.00%
🔵	io/questdb/cairo/sql/async/PageFrameReduceTask.java	10	10	100.00%
🔵	io/questdb/griffin/engine/orderby/SortedRecordCursorFactory.java	2	2	100.00%
🔵	io/questdb/cairo/sql/PageFrameMemoryPool.java	4	4	100.00%
🔵	io/questdb/griffin/engine/table/BwdTableReaderPageFrameCursor.java	1	1	100.00%
🔵	io/questdb/griffin/SqlCodeGenerator.java	37	37	100.00%
🔵	io/questdb/cairo/CairoConfigurationWrapper.java	1	1	100.00%
🔵	io/questdb/cairo/mv/MatViewRefreshSqlExecutionContext.java	1	1	100.00%

bluestreak01

great job @puzpuzpuz 👍

puzpuzpuz · 2025-08-06T18:10:34Z

@bluestreak01 thanks for the review!

perf(sql): parallel execution of ORDER BY + LIMIT queries

489005e

puzpuzpuz self-assigned this Jul 18, 2025

puzpuzpuz added SQL Issues or changes relating to SQL execution Performance Performance improvements labels Jul 18, 2025

puzpuzpuz and others added 4 commits July 18, 2025 13:48

Fix pgwire tests

3fb7f5e

Fix more tests

d5df948

Merge branch 'master' into puzpuzpuz_parallel_top_k

acb3e80

Fix query cache clean-up on HTTP connection close

c165a1c

puzpuzpuz marked this pull request as ready for review July 18, 2025 18:32

puzpuzpuz added 2 commits July 18, 2025 21:39

Fix query cache clean-up on PGWire connection close

0fa27c6

Fix javadoc

4be82e9

puzpuzpuz marked this pull request as draft July 18, 2025 19:05

puzpuzpuz marked this pull request as ready for review July 18, 2025 19:12

Add test with thread-unsafe order by expression

242a66a

bluestreak01 added 2 commits August 6, 2025 13:13

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

7c14169

…top_k

merge fallout

8ff6098

bluestreak01 reviewed Aug 6, 2025

View reviewed changes

core/src/main/java/io/questdb/griffin/engine/table/AsyncTopKRecordCursor.java Show resolved Hide resolved

bluestreak01 and others added 2 commits August 6, 2025 13:47

formatting

105fa0e

Add parallel top K methods to sql execution context

23e378a

puzpuzpuz changed the title ~~perf(sql): parallel execution of ORDER BY + LIMIT queries~~ perf(sql): parallel execution of ORDER BY + LIMIT queries (up to 10x speed-up) Aug 6, 2025

bluestreak01 reviewed Aug 6, 2025

View reviewed changes

core/src/main/java/io/questdb/cairo/sql/async/PageFrameReduceTask.java Outdated Show resolved Hide resolved

puzpuzpuz added 2 commits August 6, 2025 16:18

Address nit

5d2a3f2

Minor clean up

88219f6

bluestreak01 reviewed Aug 6, 2025

View reviewed changes

core/src/test/java/io/questdb/test/griffin/OrderByTimeoutTest.java Outdated Show resolved Hide resolved

Fix timeout test

3ed278b

bluestreak01 reviewed Aug 6, 2025

View reviewed changes

core/src/main/java/io/questdb/griffin/engine/table/AsyncTopKRecordCursorFactory.java Show resolved Hide resolved

puzpuzpuz and others added 3 commits August 6, 2025 17:11

Address review comment

06511f2

fix test

1cf9d6b

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

aa63aa6

…top_k

bluestreak01 approved these changes Aug 6, 2025

View reviewed changes

bluestreak01 merged commit 3f2d2ce into master Aug 6, 2025
35 checks passed

bluestreak01 deleted the puzpuzpuz_parallel_top_k branch August 6, 2025 18:00

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

perf(sql): parallel execution of ORDER BY + LIMIT queries (up to 10x speed-up)#5957

perf(sql): parallel execution of ORDER BY + LIMIT queries (up to 10x speed-up)#5957
bluestreak01 merged 18 commits intomasterfrom
puzpuzpuz_parallel_top_k

puzpuzpuz commented Jul 18, 2025 •

edited

Loading

Uh oh!

bluestreak01 commented Jul 19, 2025

Uh oh!

puzpuzpuz commented Jul 19, 2025

Uh oh!

coderabbitai bot commented Aug 6, 2025

Review skipped

Chat

Support

CodeRabbit Commands (Invoked using PR comments)

Other keywords and placeholders

Documentation and Community

Uh oh!

Uh oh!

bluestreak01 commented Aug 6, 2025

Uh oh!

Uh oh!

Uh oh!

Uh oh!

glasstiger commented Aug 6, 2025

Uh oh!

bluestreak01 left a comment

Uh oh!

Uh oh!

puzpuzpuz commented Aug 6, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

puzpuzpuz commented Jul 18, 2025 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Design

Benchmarks

Uh oh!

bluestreak01 commented Jul 19, 2025

Uh oh!

puzpuzpuz commented Jul 19, 2025

Uh oh!

coderabbitai bot commented Aug 6, 2025

Review skipped

Chat

Support

CodeRabbit Commands (Invoked using PR comments)

Other keywords and placeholders

Documentation and Community

Uh oh!

Uh oh!

bluestreak01 commented Aug 6, 2025

Uh oh!

Uh oh!

Uh oh!

Uh oh!

glasstiger commented Aug 6, 2025

[PR Coverage check]

file detail

Uh oh!

bluestreak01 left a comment

Choose a reason for hiding this comment

Uh oh!

Uh oh!

puzpuzpuz commented Aug 6, 2025

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

puzpuzpuz commented Jul 18, 2025 •

edited

Loading