Optimize performance with lazy projection to avoid reading unused columns by wudidapaopao · Pull Request #55518 · ClickHouse/ClickHouse

wudidapaopao · 2023-10-11T16:13:29Z

Changelog category (leave one):

Performance Improvement

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

Optimize performance with lazy projection to avoid reading unused columns.

Documentation entry for user-facing changes

Documentation is written (mandatory for new features)

Information about CI checks: https://clickhouse.com/docs/en/development/continuous-integration/

CLAassistant · 2023-10-11T16:13:38Z

All committers have signed the CLA.

wudidapaopao · 2023-10-11T16:29:14Z

@alexey-milovidov Based on #45868, lazy projection has been implemented to improve performance.
The current code implementation is quite rudimentary, but it still has some effectiveness for som queries.
For example, performance of query Q23 in ClickBench has been significantly improved.

SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
Before：Elapsed: 1.676 sec. Processed 8.87 million rows
After：Elapsed: 0.846 sec. Processed 8.87 million rows

robot-ch-test-poll3 · 2023-10-11T18:36:35Z

This is an automated comment for commit 52e58b8 with description of existing statuses. It's updated for the latest CI running

❌ Click here to open a full report in a separate page

Check name	Description	Status
Stateless tests	Runs stateless functional tests for ClickHouse binaries built in various configurations -- release, debug, with sanitizers, etc	❌ failure

Successful checks

Check name	Description	Status
AST fuzzer	Runs randomly generated queries to catch program errors. The build type is optionally given in parenthesis. If it fails, ask a maintainer for help	✅ success
Builds	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
BuzzHouse (asan)	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
BuzzHouse (debug)	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
BuzzHouse (msan)	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
BuzzHouse (tsan)	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
BuzzHouse (ubsan)	There's no description for the check yet, please add it to tests/ci/ci_config.py:CHECK_DESCRIPTIONS	✅ success
ClickBench	Runs ClickBench with instant-attach table	✅ success
Compatibility check	Checks that clickhouse binary runs on distributions with old libc versions. If it fails, ask a maintainer for help	✅ success
Docker keeper image	The check to build and optionally push the mentioned image to docker hub	✅ success
Docker server image	The check to build and optionally push the mentioned image to docker hub	✅ success
Docs check	Builds and tests the documentation	✅ success
Fast test	Normally this is the first check that is ran for a PR. It builds ClickHouse and runs most of stateless functional tests, omitting some. If it fails, further checks are not started until it is fixed. Look at the report to see which tests fail, then reproduce the failure locally as described here	✅ success
Install packages	Checks that the built packages are installable in a clear environment	✅ success
Integration tests	The integration tests report. In parenthesis the package type is given, and in square brackets are the optional part/total tests	✅ success
Performance Comparison	Measure changes in query performance. The performance test report is described in detail here. In square brackets are the optional part/total tests	✅ success
Stress test	Runs stateless functional tests concurrently from several clients to detect concurrency-related errors	✅ success
Style check	Runs a set of checks to keep the code style clean. If some of tests failed, see the related log from the report	✅ success
Unit tests	Runs the unit tests for different release types	✅ success
Upgrade check	Runs stress tests on server version from last release and then tries to upgrade it to the version from the PR. It checks if the new server can successfully startup without any errors, crashes or sanitizer asserts	✅ success

xingyingone

LGTM

amosbird · 2023-10-12T09:02:21Z

src/Core/Settings.h

    M(Bool, query_plan_aggregation_in_order, true, "Use query plan for aggregation-in-order optimisation", 0) \
    M(Bool, query_plan_remove_redundant_sorting, true, "Remove redundant sorting in query plan. For example, sorting steps related to ORDER BY clauses in subqueries", 0) \
    M(Bool, query_plan_remove_redundant_distinct, true, "Remove redundant Distinct step in query plan", 0) \
+    M(Bool, query_plan_optimize_lazy_projection, false, "Use query plan for lazy projection optimisation", 0) \


Better to use query_plan_optimize_lazy_materialization

Thank you again for taking the time to review my work. I have renamed it to query_plan_optimize_lazy_materialization.

baibaichen · 2023-10-16T03:05:23Z

@alexey-milovidov Based on #45868, lazy projection has been implemented to improve performance. The current code implementation is quite rudimentary, but it still has some effectiveness for som queries. For example, performance of query Q23 in ClickBench has been significantly improved.
SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
Before：Elapsed: 0.846 sec. Processed 8.87 million rows
After：Elapsed: 1.676 sec. Processed 8.87 million rows

Before 0.8 vs after 1.6， more slow？

wudidapaopao · 2023-10-16T03:12:18Z

@alexey-milovidov Based on #45868, lazy projection has been implemented to improve performance. The current code implementation is quite rudimentary, but it still has some effectiveness for som queries. For example, performance of query Q23 in ClickBench has been significantly improved.
SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime LIMIT 10;
Before：Elapsed: 0.846 sec. Processed 8.87 million rows
After：Elapsed: 1.676 sec. Processed 8.87 million rows
Before 0.8 vs after 1.6， more slow？

I apologize for my mistake. It has been corrected.

lgbo-ustc · 2023-10-16T08:04:03Z

Out of curiosity, Could the Columnlazy be implemented by ColumnFunction ?

wudidapaopao · 2023-10-16T16:17:35Z

Out of curiosity, Could the Columnlazy be implemented by ColumnFunction ?

Both 'ColumnLazy' and 'ColumnFunction' imply delayed execution. While it is possible to achieve the same result using 'ColumnFunction', it may not appear as intuitive, convenient, and concise as using 'ColumnLazy' directly.

baibaichen · 2023-10-18T14:36:31Z

It looks like Columnlazy could be more general, for example:

In case of a join b join c, we could materialize column after all joins are done, which could benefit from reduced materialization.
Beyond reading from mergetree, it also useful for reading from Parquet or ORC

alexey-milovidov · 2023-10-22T00:39:06Z

src/Columns/ColumnLazy.h

+private:
+    friend class COWHelper<IColumn, ColumnLazy>;
+
+    WrappedPtr part_nums;


I thought it would store a callback that encapsulates all the details, and you initialize the column with this callback. It will be useful for any kind of delayed reading or calculations.

If I understand correctly, I need to encapsulate row_nums and part_nums into an array. Different delayed executions may require different column arrays. It is similar to ColumnFunction.

src/Processors/QueryPlan/Optimizations/optimizeLazyMaterialization.cpp

src/Processors/QueryPlan/ReadFromMergeTree.cpp

src/Storages/MergeTree/MergeTreeIndexGranularity.cpp

src/Storages/MergeTree/MergeTreeIndexGranularity.h

src/Storages/MergeTree/MergeTreeSelectProcessor.cpp

src/Storages/MergeTree/MergeTreeTransform.cpp

tests/queries/0_stateless/02813_optimize_lazy_materialization.sql

alexey-milovidov · 2023-11-19T10:35:37Z

This is a very good feature, that I dreamed of! But we need to make ColumnLazy more generic - containing an opaque callback to transform it to a full column.

wudidapaopao · 2023-11-29T16:20:08Z

It looks like Columnlazy could be more general, for example:

In case of a join b join c, we could materialize column after all joins are done, which could benefit from reduced materialization.

Beyond reading from mergetree, it also useful for reading from Parquet or ORC

Yes, although currently only the lazy read of MergeTree has been implemented.

wudidapaopao · 2023-11-29T16:31:00Z

This is a very good feature, that I dreamed of! But we need to make ColumnLazy more generic - containing an opaque callback to transform it to a full column.

Reimplemented ColumnLazy, adding the IColumnLazyHelper callback interface to abstract away related details.

clickhouse-gh · 2025-03-22T20:51:58Z

Workflow [PR], commit [73ad4c2]

clickhouse-gh · 2025-03-23T20:56:25Z

Workflow [PR], commit [5f94c77]

…ze_lazy_projection

clickhouse-gh · 2025-03-24T10:19:35Z

Workflow [PR], commit [2b9a0d9]

clickhouse-gh · 2025-03-24T14:41:20Z

Workflow [PR], commit [53a186d]

clickhouse-gh · 2025-03-24T21:02:48Z

Workflow [PR], commit [0dc1c3f]

devcrafter · 2025-03-24T21:04:35Z

Stateless tests (tsan, s3 storage, 1/3) — Server died, fail - should be fixed by #78205

* [GLUTEN-1632][CH]Daily Update Clickhouse Version (20250326) * Fix build due to ClickHouse/ClickHouse#55518 * Fix Build due to ClickHouse/ClickHouse#77013 * Fix gtest build due to ClickHouse/ClickHouse#77895 * disbable query_plan_optimize_lazy_materialization due to #9141 --------- Co-authored-by: kyligence-git <[email protected]> Co-authored-by: Chang chen <[email protected]>

Support lazy projection

86c938a

robot-ch-test-poll3 added the pr-performance Pull request with some performance improvements label Oct 11, 2023

xingyingone approved these changes Oct 12, 2023

View reviewed changes

amosbird reviewed Oct 12, 2023

View reviewed changes

alexey-milovidov added the can be tested Allows running workflows for external contributors label Oct 13, 2023

fix style

ce68887

wudidapaopao force-pushed the wudidapaopao/optimize_lazy_projection branch from 9303fc9 to ce68887 Compare October 16, 2023 15:39

empty commit

18ffe8a

wudidapaopao added 4 commits October 19, 2023 00:36

fix functional test

93bf069

fix style

b8c8a8d

fix style

5786a42

fix functional test

9ca0e72

alexey-milovidov reviewed Oct 22, 2023

View reviewed changes

wudidapaopao added 4 commits October 23, 2023 22:25

fix functional test

baf9c4d

test

bb7f1da

empty commit

1b3bd71

Merge branch 'master' into wudidapaopao/optimize_lazy_projection

c9b7028

amosbird reviewed Nov 14, 2023

View reviewed changes

Reimplement the interface of ColumnLazy

a19700a

devcrafter enabled auto-merge March 22, 2025 17:53

Merge branch 'master' into wudidapaopao/optimize_lazy_projection

73ad4c2

devcrafter disabled auto-merge March 23, 2025 15:16

devcrafter enabled auto-merge March 23, 2025 15:16

Merge branch 'master' into wudidapaopao/optimize_lazy_projection

5f94c77

Merge remote-tracking branch 'origin/master' into wudidapaopao/optimi…

2b9a0d9

…ze_lazy_projection

Merge branch 'master' into wudidapaopao/optimize_lazy_projection

53a186d

Merge branch 'master' into wudidapaopao/optimize_lazy_projection

0dc1c3f

devcrafter added this pull request to the merge queue Mar 25, 2025

Merged via the queue into ClickHouse:master with commit 704e91c Mar 25, 2025
122 checks passed

robot-clickhouse-ci-2 added the pr-synced-to-cloud The PR is synced to the cloud repo label Mar 25, 2025

baibaichen added a commit to Kyligence/gluten that referenced this pull request Mar 26, 2025

Fix build due to ClickHouse/ClickHouse#55518

221ad84

baibaichen mentioned this pull request Mar 26, 2025

[CH] Support lazy projection apache/gluten#9141

Open

baibaichen added a commit to Kyligence/gluten that referenced this pull request Mar 26, 2025

Fix build due to ClickHouse/ClickHouse#55518

b38483e

UnamedRus mentioned this pull request Apr 5, 2025

ProfileEvent for how many granules were read by LazyMaterialization #78716

Open

Avogar mentioned this pull request Apr 10, 2025

Fix deserialization of variant discriminators with basic format in compact parts #79000

Merged

1 task

acking-you mentioned this pull request Apr 18, 2025

[Epic] Dynamic filtering related items apache/datafusion#15512

Open

4 tasks

orloffv mentioned this pull request Apr 22, 2025

Plans to update ClickHouse to the latest version (25.4) in CHYT? ytsaurus/ytsaurus#1231

Open

acking-you mentioned this pull request Apr 23, 2025

Make ClickBench Q23 Go Faster apache/datafusion#15177

Closed

bharatnc mentioned this pull request Apr 30, 2025

SELECT * ORDER BY rand() LIMIT 10 triggers DB::Exception: Block structure mismatch #79730

Closed

antaljanosbenjamin mentioned this pull request Jul 30, 2025

SELECT ... ORDER BY ... LIMIT ... query with external sort results in CORRUPTED_DATA #84718

Closed

alesapin mentioned this pull request Oct 28, 2025

Implement Lazy Materialization for DataLakes #89105

Open

Conversation

wudidapaopao commented Oct 11, 2023 • edited by rschu1ze Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Changelog category (leave one):

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

Documentation entry for user-facing changes

Uh oh!

CLAassistant commented Oct 11, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

wudidapaopao commented Oct 11, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

robot-ch-test-poll3 commented Oct 11, 2023 • edited by robot-ch-test-poll Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

xingyingone left a comment

Choose a reason for hiding this comment

Uh oh!

amosbird Oct 12, 2023

Choose a reason for hiding this comment

Uh oh!

wudidapaopao Oct 16, 2023

Choose a reason for hiding this comment

Uh oh!

baibaichen commented Oct 16, 2023

Uh oh!

wudidapaopao commented Oct 16, 2023

Uh oh!

lgbo-ustc commented Oct 16, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

wudidapaopao commented Oct 16, 2023 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

baibaichen commented Oct 18, 2023

Uh oh!

alexey-milovidov Oct 22, 2023

Choose a reason for hiding this comment

Uh oh!

wudidapaopao Oct 31, 2023

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!

Uh oh!

alexey-milovidov commented Nov 19, 2023

Uh oh!

wudidapaopao commented Nov 29, 2023

Uh oh!

wudidapaopao commented Nov 29, 2023

Uh oh!

clickhouse-gh bot commented Mar 22, 2025

Uh oh!

clickhouse-gh bot commented Mar 23, 2025

Uh oh!

clickhouse-gh bot commented Mar 24, 2025

Uh oh!

clickhouse-gh bot commented Mar 24, 2025

Uh oh!

clickhouse-gh bot commented Mar 24, 2025

Uh oh!

devcrafter commented Mar 24, 2025

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

15 participants

wudidapaopao commented Oct 11, 2023 •

edited by rschu1ze

Loading

CLAassistant commented Oct 11, 2023 •

edited

Loading

wudidapaopao commented Oct 11, 2023 •

edited

Loading

robot-ch-test-poll3 commented Oct 11, 2023 •

edited by robot-ch-test-poll

Loading

lgbo-ustc commented Oct 16, 2023 •

edited

Loading

wudidapaopao commented Oct 16, 2023 •

edited

Loading