Revert "Revert "Merge pull request #93542 from scanhex12/multistage_prewhere"" by scanhex12 · Pull Request #95565 · ClickHouse/ClickHouse

scanhex12 · 2026-01-29T18:52:52Z

…rewhere""

clickhouse-gh · 2026-01-29T18:53:35Z

Workflow [PR], commit [67dc5ff]

Summary: ❌

job_name	test_name	status	info
Stateless tests (arm_binary, sequential)		failure
	00992_system_parts_race_condition_zookeeper_long	FAIL	cidb
Stress test (amd_tsan)		failure
	Logical error: Block structure mismatch in A stream: different number of columns: (STID: 0993-2da1)	FAIL	cidb, issue
Stress test (arm_asan)		failure
	Logical error: Block structure mismatch in A stream: different number of columns: (STID: 0993-38e6)	FAIL	cidb, issue
Stress test (arm_asan, s3)		failure
	Logical error: Cannot modify size limits to A in size and B in elements: not enough space freed. Current size: C/D, elements: E/F (G) (STID: 4012-44fe)	FAIL	cidb
Stress test (amd_msan)		failure
	Logical error: Block structure mismatch in A stream: different number of columns: (STID: 0993-38e6)	FAIL	cidb, issue

alexey-milovidov · 2026-01-29T18:56:23Z

@scanhex12, attach ClickBench results here before merging.

alexey-milovidov

.

alexey-milovidov · 2026-01-29T18:57:09Z

@scanhex12, create isolated tests that reproduce the bug deterministically.

alexey-milovidov

Wow, those are seriously good speed-ups!

al13n321 · 2026-02-02T22:10:23Z

src/Processors/Formats/Impl/Parquet/Reader.cpp

+                else
+                {
+                    size_t remaining = rows_total - row_subidx;
+                    const UInt8 * const first_one = static_cast<const UInt8 *>(memchr(filter_base + row_subidx, 1, remaining));


insert into function file('t.parquet') select if(number%10,2,0) as x from numbers(100) settings engine_file_truncate_on_insert=1; select count() from file('t.parquet') prewhere x;

seems like it works:

:) insert into function file('t.parquet') select if(number%10,2,0) as x from numbers(100) settings engine_file_truncate_on_insert=1; select count() from file('t.parquet') prewhere x; INSERT INTO FUNCTION file('t.parquet') SETTINGS engine_file_truncate_on_insert = 1 SELECT if(number % 10, 2, 0) AS x FROM numbers(100) SETTINGS engine_file_truncate_on_insert = 1 Query id: 8692db55-1924-460c-9015-b25fdca9180e Ok. 0 rows in set. Elapsed: 0.005 sec. SELECT count() FROM file('t.parquet') PREWHERE x Query id: f8bb1455-43fc-4f42-9174-9418a6278c94 ┌─count()─┐ 1. │ 90 │ └─────────┘ 1 row in set. Elapsed: 0.004 sec.

src/Processors/Formats/Impl/Parquet/Reader.cpp

scanhex12 · 2026-02-03T01:56:11Z

Sorry, there were segfaults in the previous results. New benchmarks:

[0.108, 0.032, 0.033],
[0.311, 0.077, 0.084],
[0.395, 0.114, 0.113],
[1.005, 0.123, 0.126],
[1.106, 0.352, 0.345],
[1.592, 0.416, 0.421],
[0.161, 0.081, 0.077],
[0.145, 0.069, 0.069],
[1.429, 0.451, 0.469],
[2.230, 0.491, 0.498],
[1.245, 0.305, 0.281],
[1.332, 0.316, 0.318],
[1.641, 0.469, 0.465],
[3.515, 0.671, 0.674],
[1.746, 0.525, 0.522],
[1.090, 0.410, 0.418],
[3.790, 1.192, 1.217],
[3.356, 0.850, 0.837],
[6.873, 2.241, 2.235],
[0.716, 0.108, 0.114],
[13.859, 0.903, 0.920],
[16.193, 1.206, 1.202],
[31.063, 2.181, 2.221],
[77.281, 4.850, 4.944],
[4.056, 0.422, 0.417],
[1.506, 0.237, 0.246],
[4.007, 0.420, 0.419],
[14.100, 1.171, 1.205],
[12.082, 4.312, 4.295],
[0.350, 0.110, 0.098],
[3.800, 0.570, 0.577],
[8.773, 0.776, 0.791],
[9.951, 4.836, 5.090],
[14.385, 1.888, 1.897],
[14.242, 1.885, 1.873],
[0.583, 0.342, 0.353],
[0.464, 0.131, 0.145],
[0.354, 0.107, 0.105],
[0.425, 0.099, 0.100],
[0.470, 0.157, 0.159],
[0.406, 0.075, 0.070],
[0.296, 0.056, 0.075],
[0.295, 0.066, 0.063],

Master branch:

[0.122, 0.038, 0.038],
[0.209, 0.075, 0.072],
[0.363, 0.110, 0.108],
[0.919, 0.112, 0.116],
[1.066, 0.385, 0.384],
[1.571, 0.455, 0.453],
[0.228, 0.074, 0.077],
[0.334, 0.073, 0.070],
[1.455, 0.441, 0.448],
[2.136, 0.507, 0.504],
[1.238, 0.260, 0.253],
[1.276, 0.262, 0.248],
[1.738, 0.467, 0.473],
[3.492, 0.746, 0.706],
[1.858, 0.560, 0.570],
[1.117, 0.444, 0.436],
[4.103, 1.253, 1.274],
[3.323, 1.019, 0.996],
[6.914, 2.376, 2.429],
[0.735, 0.119, 0.118],
[13.961, 0.754, 0.765],
[16.228, 1.245, 1.209],
[30.963, 1.910, 1.941],
[77.794, 5.563, 16.375],
[3.988, 0.328, 0.317],
[1.535, 0.233, 0.236],
[4.013, 0.334, 0.333],
[14.234, 1.064, 0.982],
[11.707, 4.765, 4.711],
[0.322, 0.106, 0.099],
[3.796, 0.563, 0.575],
[9.547, 0.748, 0.724],
[10.159, 5.604, 5.113],
[14.420, 4.675, 4.663],
[14.472, 4.760, 4.777],
[0.861, 0.349, 0.346],
[0.485, 0.174, 0.160],
[0.375, 0.127, 0.137],
[0.501, 0.100, 0.104],
[0.507, 0.169, 0.175],
[0.323, 0.072, 0.076],
[0.422, 0.057, 0.058],
[0.242, 0.064, 0.065],

Speedups (greater is better):

[1.130, 1.188, 1.152],
[0.672, 0.974, 0.857],
[0.919, 0.965, 0.956],
[0.914, 0.911, 0.921],
[0.964, 1.094, 1.113],
[0.987, 1.094, 1.076],
[1.416, 0.914, 1.000],
[2.303, 1.058, 1.014],
[1.018, 0.978, 0.955],
[0.958, 1.033, 1.012],
[0.994, 0.852, 0.900],
[0.958, 0.829, 0.780],
[1.059, 0.996, 1.017],
[0.993, 1.112, 1.047],
[1.064, 1.067, 1.092],
[1.025, 1.083, 1.043],
[1.083, 1.051, 1.047],
[0.990, 1.199, 1.190],
[1.006, 1.060, 1.087],
[1.027, 1.102, 1.035],
[1.007, 0.835, 0.832],
[1.002, 1.032, 1.006],
[0.997, 0.876, 0.874],
[1.007, 1.147, 3.312],
[0.983, 0.777, 0.760],
[1.019, 0.983, 0.959],
[1.001, 0.795, 0.795],
[1.010, 0.909, 0.815],
[0.969, 1.105, 1.097],
[0.920, 0.964, 1.010],
[0.999, 0.988, 0.997],
[1.088, 0.964, 0.915],
[1.021, 1.159, 1.005],
[1.002, 2.476, 2.458],
[1.016, 2.525, 2.550],
[1.477, 1.020, 0.980],
[1.045, 1.328, 1.103],
[1.059, 1.187, 1.305],
[1.179, 1.010, 1.040],
[1.079, 1.076, 1.101],
[0.796, 0.960, 1.086],
[1.426, 1.018, 0.773],
[0.820, 0.970, 1.032],

Geometric mean becomes better:

old: 1.8538961059026726
new: 1.7885948006744214
ratio new/old: 0.9647761786540592
old: 0.39366110244108643
new: 0.37386989623272876
ratio new/old: 0.9497252685479142
old: 0.4010315651650805
new: 0.37822098786461256
ratio new/old: 0.9431202446842851

scanhex12 · 2026-02-03T23:20:55Z

Results on my mac seems better:

with multistage prewhere:

[0.026, 0.013, 0.014],
[0.084, 0.062, 0.061],
[0.122, 0.100, 0.099],
[0.170, 0.115, 0.117],
[0.684, 0.671, 0.676],
[0.759, 0.662, 0.735],
[0.079, 0.063, 0.061],
[0.088, 0.080, 0.075],
[0.801, 0.762, 0.750],
[0.944, 0.907, 0.918],
[0.344, 0.297, 0.298],
[0.360, 0.298, 0.306],
[1.654, 1.701, 1.649],
[2.061, 2.019, 2.017],
[1.875, 1.954, 1.914],
[0.884, 0.855, 0.860],
[2.591, 2.546, 2.600],
[2.082, 2.170, 2.165],
[5.586, 4.031, 3.813],
[0.162, 0.115, 0.117],
[1.758, 1.027, 1.015],
[2.229, 1.296, 9.318],
[3.270, 1.919, 1.947],
[10.741, 12.882, 13.060],
[0.690, 0.572, 0.582],
[0.427, 0.379, 0.357],
[0.743, 0.565, 0.559],
[2.362, 1.592, 1.570],
[4.955, 4.655, 4.596],
[0.098, 0.072, 0.074],
[1.380, 1.313, 1.223],
[1.993, 1.795, 1.848],
[4.726, 4.252, 4.249],
[4.469, 4.152, 4.051],
[4.260, 3.947, 4.052],
[0.734, 0.719, 0.702],
[0.142, 0.089, 0.097],
[0.079, 0.051, 0.052],
[0.089, 0.044, 0.045],
[0.215, 0.163, 0.161],
[0.070, 0.035, 0.034],
[0.057, 0.031, 0.030],
[0.049, 0.025, 0.025],

On master:

[0.021, 0.014, 0.019],
[0.098, 0.073, 0.078],
[0.121, 0.104, 0.099],
[0.187, 0.131, 0.128],
[0.907, 0.765, 0.734],
[0.986, 0.811, 0.750],
[0.091, 0.076, 0.076],
[0.120, 0.096, 0.098],
[1.103, 0.929, 0.928],
[1.395, 1.144, 1.267],
[0.389, 0.346, 0.349],
[0.430, 0.365, 0.382],
[2.175, 1.656, 1.729],
[2.528, 2.258, 2.235],
[2.228, 2.132, 2.191],
[0.999, 0.936, 0.961],
[4.157, 2.803, 2.952],
[2.478, 2.293, 2.238],
[5.960, 5.239, 4.681],
[0.196, 0.156, 0.147],
[1.612, 0.988, 0.986],
[2.220, 1.357, 1.324],
[3.911, 2.344, 2.285],
[9.406, 12.376, 12.641],
[0.737, 0.608, 0.577],
[0.484, 0.411, 0.417],
[0.662, 0.568, 0.593],
[2.037, 1.449, 1.361],
[5.412, 7.548, 6.949],
[0.099, 0.066, 0.071],
[1.427, 1.421, 1.398],
[2.043, 1.993, 1.962],
[5.377, 5.565, 6.169],
[4.587, 5.706, 5.667],
[4.308, 6.274, 4.626],
[0.842, 0.772, 0.832],
[0.144, 0.095, 0.098],
[0.081, 0.051, 0.052],
[0.084, 0.044, 0.042],
[0.225, 0.173, 0.166],
[0.054, 0.027, 0.032],
[0.048, 0.023, 0.022],
[0.047, 0.023, 0.023],

Geometric mean (~10% speedup):

scanhex12@kostya:~/ClickHouse$ python3 multistage_prewhere_bench.py 
old: 0.638174778225622
new: 0.5868357246222701
ratio new/old: 0.9195533020811403
old: 0.511535694692919
new: 0.4630350838001719
ratio new/old: 0.9051862628631172
old: 0.5129878039956252
new: 0.484689788767942
ratio new/old: 0.9448368655019244

Requests only with multistage prewhere (~20-25% speedup):

scanhex12@kostya:~/ClickHouse$ python3 multistage_prewhere_bench.py 
old: 0.1107373898552152
new: 0.088334511388541
ratio new/old: 0.7976936381111648
old: 0.06701216701068101
new: 0.05088235139940199
ratio new/old: 0.7593001938184135
old: 0.06839137376304578
new: 0.051275235537945
ratio new/old: 0.7497324986568815

Revert "Revert "Merge pull request #93542 from scanhex12/multistage_p…

d03f8a3

…rewhere""

clickhouse-gh bot added the pr-not-for-changelog This PR should not be mentioned in the changelog label Jan 29, 2026

alexey-milovidov requested changes Jan 29, 2026

View reviewed changes

scanhex12 added 6 commits January 29, 2026 18:59

fix issues

af30100

more tests

cd1e33f

better

04a0fcd

fix assert

db880f1

add mem check

001d775

refactoring

710188c

scanhex12 mentioned this pull request Jan 30, 2026

enable prewhere for iceberg #95476

Merged

1 task

alexey-milovidov self-assigned this Jan 30, 2026

alexey-milovidov approved these changes Jan 30, 2026

View reviewed changes

fix tests

40eb5f5

al13n321 approved these changes Jan 30, 2026

View reviewed changes

al13n321 reviewed Feb 2, 2026

View reviewed changes

src/Processors/Formats/Impl/Parquet/Reader.cpp Outdated Show resolved Hide resolved

al13n321 reviewed Feb 2, 2026

View reviewed changes

src/Processors/Formats/Impl/Parquet/Reader.cpp Outdated Show resolved Hide resolved

src/Processors/Formats/Impl/Parquet/Reader.cpp Outdated Show resolved Hide resolved

push down filter to decoders

a68e002

scanhex12 force-pushed the revert-95496-revert-multistage-prewhere-parquet branch from a0a8eae to a68e002 Compare February 3, 2026 00:03

add lazy filter

274c850

scanhex12 added 6 commits February 3, 2026 02:00

fix build

da30e96

fix build & fix tests

5a9009f

fix

ee3bb1f

fix test

64419cb

fix tests

bb5c216

remove useless code

3101453

remove accum filter

eba0510

scanhex12 added 5 commits February 3, 2026 23:29

fix flaky test

4ba3b7e

fix test

d4c7556

fix

32cffc6

fix

73754fb

fix test

67dc5ff

scanhex12 enabled auto-merge February 4, 2026 09:02

scanhex12 added this pull request to the merge queue Feb 4, 2026

Merged via the queue into master with commit a358daa Feb 4, 2026
125 of 131 checks passed

scanhex12 deleted the revert-95496-revert-multistage-prewhere-parquet branch February 4, 2026 09:22

robot-ch-test-poll1 added the pr-synced-to-cloud The PR is synced to the cloud repo label Feb 4, 2026

This was referenced Feb 4, 2026

Logical error: 'state.column' (STID: 2508-3a62) #95219

Closed

Logical error: 'row_subgroup.filter.rows_pass > 0' (STID: 2508-43d8) #95536

Closed

Revert "Revert "Revert "Merge pull request #93542 from scanhex12/multistage_prewhere""" #96028

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Revert "Revert "Merge pull request #93542 from scanhex12/multistage_prewhere""#95565

Revert "Revert "Merge pull request #93542 from scanhex12/multistage_prewhere""#95565
scanhex12 merged 22 commits intomasterfrom
revert-95496-revert-multistage-prewhere-parquet

scanhex12 commented Jan 29, 2026

Uh oh!

clickhouse-gh bot commented Jan 29, 2026 •

edited

Loading

Uh oh!

alexey-milovidov commented Jan 29, 2026

Uh oh!

alexey-milovidov left a comment

Uh oh!

alexey-milovidov commented Jan 29, 2026

Uh oh!

alexey-milovidov left a comment

Uh oh!

al13n321 Feb 2, 2026

Uh oh!

scanhex12 Feb 3, 2026

Uh oh!

Uh oh!

Uh oh!

Uh oh!

scanhex12 commented Feb 3, 2026

Uh oh!

scanhex12 commented Feb 3, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

Conversation

scanhex12 commented Jan 29, 2026

Uh oh!

clickhouse-gh bot commented Jan 29, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

alexey-milovidov commented Jan 29, 2026

Uh oh!

alexey-milovidov left a comment

Choose a reason for hiding this comment

Uh oh!

alexey-milovidov commented Jan 29, 2026

Uh oh!

alexey-milovidov left a comment

Choose a reason for hiding this comment

Uh oh!

al13n321 Feb 2, 2026

Choose a reason for hiding this comment

Uh oh!

scanhex12 Feb 3, 2026

Choose a reason for hiding this comment

Uh oh!

Uh oh!

Uh oh!

Uh oh!

scanhex12 commented Feb 3, 2026

Uh oh!

scanhex12 commented Feb 3, 2026

Uh oh!

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

4 participants

clickhouse-gh bot commented Jan 29, 2026 •

edited

Loading