Skip to content

Projection Index Step 3: Support using normal projections as secondary index#81021

Merged
CurtizJ merged 7 commits intoClickHouse:masterfrom
amosbird:projection-index-4-post
Nov 3, 2025
Merged

Projection Index Step 3: Support using normal projections as secondary index#81021
CurtizJ merged 7 commits intoClickHouse:masterfrom
amosbird:projection-index-4-post

Conversation

@amosbird
Copy link
Copy Markdown
Collaborator

@amosbird amosbird commented May 30, 2025 •
edited by clickhouse-gh Bot
Loading

Changelog category (leave one):

  • Improvement

Changelog entry (a user-readable short description of the changes that goes into CHANGELOG.md):

This PR allows using normal projections as a secondary index. When enabled, certain query predicates can be used to read from projection parts and generate bitmaps to filter rows efficiently during the PREWHERE stage. This is the third step to implement projection index, following #80343.

Documentation entry for user-facing changes

  • Documentation is written (mandatory for new features)

Details

This also provides some references for the future implementation of #75774.

Two new settings are introduced to control this optimization:

  • max_projection_rows_to_use_projection_index: If the estimated number of rows to read from the projection is <= this value, projection index can be applied.

  • min_table_rows_to_use_projection_index: If the estimated number of rows to read from the table is >= this value, projection index will be considered.

These thresholds help balance projection index usage with its overhead in less selective queries.

Currently only point-type projection indexes are supported. Inverted projections (e.g., Array(_part_offset)) will be in next step.

@clickhouse-gh
Copy link
Copy Markdown
Contributor

clickhouse-gh Bot commented May 30, 2025 •
edited
Loading

Workflow [PR], commit [2ca9333a]

@clickhouse-gh clickhouse-gh Bot added pr-improvement Pull request with some product improvements submodule changed At least one submodule changed in this PR. labels May 30, 2025
@amosbird amosbird marked this pull request as draft May 30, 2025 06:52
@amosbird amosbird force-pushed the projection-index-4-post branch 5 times, most recently from 7b8e1e4 to 2022cce Compare May 31, 2025 16:45
@amosbird amosbird force-pushed the projection-index-4-post branch 4 times, most recently from 74b90ad to dd7dc2d Compare June 6, 2025 17:35
@amosbird amosbird marked this pull request as ready for review June 6, 2025 18:24
This was referenced Jun 7, 2025
Closed
Merged
@amosbird amosbird force-pushed the projection-index-4-post branch 2 times, most recently from 74913e7 to 2ca9333 Compare June 11, 2025 19:35
@amosbird amosbird mentioned this pull request Jun 25, 2025
Closed
1 task
@amosbird amosbird mentioned this pull request Jul 21, 2025
Closed
@EmeraldShift
Copy link
Copy Markdown
Contributor

EmeraldShift commented Jul 29, 2025

Hi! Just curious, is this feature still planned for release soon?

@amosbird
Copy link
Copy Markdown
Collaborator Author

amosbird commented Jul 29, 2025

Hi! Just curious, is this feature still planned for release soon?

This feature involves a relatively complex review, so it may take some time. It's currently pending review at #81526

@amosbird amosbird force-pushed the projection-index-4-post branch from 2ca9333 to 0c7e240 Compare August 27, 2025 15:43
@clickhouse-gh
Copy link
Copy Markdown
Contributor

clickhouse-gh Bot commented Aug 27, 2025 •
edited
Loading

Workflow [PR], commit [c671b1c]

Summary: ❌

job_name test_name status info comment
Stress test (amd_tsan) failure
Server died FAIL cidb
Hung check failed, possible deadlock found (see hung_check.log) FAIL cidb
Killed by signal (in clickhouse-server.log) FAIL cidb
Fatal message in clickhouse-server.log (see fatal_messages.txt) FAIL cidb
Killed by signal (output files) FAIL cidb
Found signal in gdb.log FAIL cidb

@amosbird amosbird force-pushed the projection-index-4-post branch 2 times, most recently from 404b994 to cb31d33 Compare August 28, 2025 02:32
@amosbird amosbird force-pushed the projection-index-4-post branch from cb31d33 to 2771328 Compare August 28, 2025 06:21
@amosbird
Copy link
Copy Markdown
Collaborator Author

amosbird commented Aug 29, 2025 

FAIL	Exception in test runner	0.13	15:34:16
Traceback (most recent call last):
  File "/usr/lib/python3.10/multiprocessing/util.py", line 357, in _exit_function
Process Process-8:

Not sure what this means.

@CurtizJ CurtizJ self-assigned this Sep 12, 2025
@meguoe
Copy link
Copy Markdown

meguoe commented Sep 17, 2025

Looking forward to this feature, not sure how much longer 😒？

CurtizJ
CurtizJ reviewed Sep 26, 2025
View reviewed changes
Copy link
Copy Markdown
Member

@CurtizJ CurtizJ left a comment

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Great PR! I've left some comments and questions.

Comment thread src/Processors/QueryPlan/Optimizations/projectionsCommon.cpp Outdated
Comment thread src/Storages/MergeTree/MergeTreeReaderIndex.cpp
Comment thread src/Processors/QueryPlan/Optimizations/projectionsCommon.cpp
Comment thread src/Storages/MergeTree/MergeTreeReadPoolBase.cpp Outdated
Comment thread src/Storages/MergeTree/MergeTreeIndexReadResultPool.cpp Outdated
Comment thread src/Storages/MergeTree/MergeTreeIndexReadResultPool.cpp
Comment thread src/Storages/MergeTree/RangesInDataPart.h
Comment thread src/Storages/MergeTree/MergeTreeIndexReadResultPool.cpp
Comment thread src/Storages/MergeTree/MergeTreeIndexReadResultPool.cpp
@EmeraldShift EmeraldShift mentioned this pull request Oct 1, 2025
Merged
@amosbird
Copy link
Copy Markdown
Collaborator Author

amosbird commented Oct 22, 2025 

Add labels [['submodule changed']]
Run command: [gh pr edit 81021 --add-label 'submodule changed']
GraphQL: Projects (classic) is being deprecated in favor of the new Projects experience, see: https://github.blog/changelog/2024-05-23-sunset-notice-projects-classic/. (repository.pullRequest.projectCards)
ERROR: command failed, exit code: 1, retry: 1/1
Traceback (most recent call last):
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/./ci/jobs/scripts/workflow_hooks/pr_description.py", line 221, in <module>
    check_labels(category, info)
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/./ci/jobs/scripts/workflow_hooks/pr_description.py", line 213, in check_labels
    Shell.check(cmd, verbose=True, strict=True)
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/ci/praktika/utils.py", line 219, in check
    cls.run(
  File "/home/ubuntu/actions-runner/_work/ClickHouse/ClickHouse/ci/praktika/utils.py", line 412, in run
    raise RuntimeError(
RuntimeError: command failed, exit code 1,
stderr:
>>>
GraphQL: Projects (classic) is being deprecated in favor of the new Projects experience, see: https://github.blog/changelog/2024-05-23-sunset-notice-projects-classic/. (repository.pullRequest.projectCards)
<<<

It seems CI is broken when trying to add submodule changed label.

@amosbird
Copy link
Copy Markdown
Collaborator Author

amosbird commented Nov 3, 2025

Stress test (amd_tsan)

[ 171221 ] {513fe965-8da1-4156-bd68-0da8611caaff} : Logical error: 'Trying to insert into runtime filter after it was marked as finished'.

Seems related to runtime filter.

@CurtizJ CurtizJ added this pull request to the merge queue Nov 3, 2025
Merged via the queue into ClickHouse:master with commit b7cefca Nov 3, 2025
122 of 124 checks passed
@robot-clickhouse-ci-1 robot-clickhouse-ci-1 added the pr-synced-to-cloud The PR is synced to the cloud repo label Nov 3, 2025
@RinChanNOWWW RinChanNOWWW mentioned this pull request Nov 18, 2025
Draft
1 task
@amosbird amosbird mentioned this pull request Nov 24, 2025
Open
@KochetovNicolai KochetovNicolai mentioned this pull request Dec 8, 2025
Open
@amosbird amosbird mentioned this pull request Dec 10, 2025
Merged
1 task
@UnamedRus UnamedRus mentioned this pull request Dec 30, 2025
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@CurtizJ CurtizJ CurtizJ approved these changes

Assignees

@CurtizJ CurtizJ

Labels

pr-improvement Pull request with some product improvements pr-synced-to-cloud The PR is synced to the cloud repo submodule changed At least one submodule changed in this PR.

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

6 participants

@amosbird @EmeraldShift @meguoe @CurtizJ @KochetovNicolai @robot-clickhouse-ci-1