Skip to content

branch-3.1: [opt](mtmv) optimize mtmv rewrite performance #49514 #52241

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Merged
morrySnow merged 2 commits into from
Jun 25, 2025
Merged

branch-3.1: [opt](mtmv) optimize mtmv rewrite performance #49514 #52241

morrySnow merged 2 commits into from
Jun 25, 2025

Conversation

@seawinde
Copy link
Contributor

@seawinde seawinde commented Jun 24, 2025 •
edited by morrySnow
Loading

Cherry-pick from #49514

@seawinde @zddr
[opt](mtmv) optimize mtmv rewrite performance (apache#49514)
42354be 
- Obtaining available materialized view adjustments after partition
pruning RBO rewrite rules. This reduces partition version comparison
overhead for partitioned materializations, thereby improving
performance.

- Set maximum time threshold for transparent query rewriting with
default value of 1000ms. Rewrite attempts will be terminated if
exceeding this threshold.
set materialized_view_rewrite_duration_threshold_ms = 1000

- Optimize code structure in transparent rewriting framework:
    1. Replace Lambda expressions with conventional for-loops
    2. Remove redundant member variables

- Cache available partitions of materialized views. Since calculating
available partitions for materialized views is time-consuming,
caching can significantly enhance performance.

Co-authored-by: zhangdong <zhangdong@selectdb.com>
@seawinde seawinde requested a review from morrySnow as a code owner June 24, 2025 12:40
@hello-stephen
Copy link
Contributor

hello-stephen commented Jun 24, 2025

Thank you for your contribution to Apache Doris.
Don't know what should be done next? See How to process your PR.

Please clearly describe your PR:

  1. What problem was fixed (it's best to include specific error reporting information). How it was fixed.
  2. Which behaviors were modified. What was the previous behavior, what is it now, why was it modified, and what possible impacts might there be.
  3. What features were added. Why was this function added?
  4. Which code was refactored and why was this part of the code refactored?
  5. Which functions were optimized and what is the difference before and after the optimization?

@seawinde
Copy link
Contributor Author

seawinde commented Jun 24, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Jun 24, 2025

TPC-H: Total hot run time: 39888 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 42354be84338b01e3f41186b3cfad827722ca09f, data reload: false

------ Round 1 ----------------------------------
q1	17635	6771	6650	6650
q2	2060	191	159	159
q3	10533	1111	1158	1111
q4	10367	755	831	755
q5	7773	2906	2864	2864
q6	220	136	139	136
q7	1018	639	620	620
q8	9370	1958	2087	1958
q9	6624	6445	6409	6409
q10	7047	2239	2306	2239
q11	468	266	256	256
q12	416	209	206	206
q13	17805	2979	2993	2979
q14	245	210	214	210
q15	523	466	454	454
q16	467	374	379	374
q17	999	559	569	559
q18	7356	6783	6648	6648
q19	1313	1003	934	934
q20	460	204	201	201
q21	3912	3186	3230	3186
q22	1086	998	980	980
Total cold run time: 107697 ms
Total hot run time: 39888 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6627	6556	6622	6556
q2	340	240	230	230
q3	2947	2793	2899	2793
q4	2068	1829	1854	1829
q5	5762	5788	5748	5748
q6	219	132	136	132
q7	2221	1815	1836	1815
q8	3380	3579	3556	3556
q9	9010	8880	9005	8880
q10	3546	3538	3564	3538
q11	601	485	505	485
q12	798	641	648	641
q13	10343	3208	3170	3170
q14	300	266	276	266
q15	523	454	473	454
q16	504	438	439	438
q17	1883	1624	1738	1624
q18	8285	7800	7832	7800
q19	1715	1549	1568	1549
q20	2043	1811	1844	1811
q21	5214	5026	5023	5023
q22	1115	1017	1048	1017
Total cold run time: 69444 ms
Total hot run time: 59355 ms

@doris-robot
Copy link

doris-robot commented Jun 24, 2025

TPC-DS: Total hot run time: 197013 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 42354be84338b01e3f41186b3cfad827722ca09f, data reload: false

query1	1311	916	913	913
query2	6264	1994	1971	1971
query3	10831	4317	4322	4317
query4	61083	29404	23568	23568
query5	5201	469	481	469
query6	403	183	183	183
query7	5466	321	324	321
query8	316	235	225	225
query9	8810	2618	2600	2600
query10	465	284	261	261
query11	17530	14940	15915	14940
query12	159	108	107	107
query13	1470	451	425	425
query14	10139	6840	6684	6684
query15	209	191	178	178
query16	7138	484	453	453
query17	1206	601	590	590
query18	1921	332	308	308
query19	211	157	161	157
query20	112	115	116	115
query21	216	109	104	104
query22	4797	4510	4719	4510
query23	34621	34120	34328	34120
query24	6899	2948	2894	2894
query25	544	399	415	399
query26	658	171	169	169
query27	2608	368	357	357
query28	4185	2183	2165	2165
query29	683	442	425	425
query30	240	158	154	154
query31	997	830	830	830
query32	67	56	57	56
query33	418	308	304	304
query34	910	503	511	503
query35	850	733	739	733
query36	1040	958	945	945
query37	119	72	68	68
query38	4045	4035	3984	3984
query39	1494	1477	1512	1477
query40	202	98	97	97
query41	46	47	48	47
query42	124	103	105	103
query43	526	486	489	486
query44	1198	833	818	818
query45	180	170	170	170
query46	1136	753	754	753
query47	2069	1963	1940	1940
query48	450	350	362	350
query49	722	411	377	377
query50	844	428	428	428
query51	7373	7177	7275	7177
query52	106	98	89	89
query53	251	195	188	188
query54	558	465	458	458
query55	77	78	82	78
query56	261	255	250	250
query57	1345	1227	1245	1227
query58	215	211	219	211
query59	3257	3035	3094	3035
query60	305	265	254	254
query61	133	104	102	102
query62	758	680	695	680
query63	213	182	180	180
query64	1363	653	642	642
query65	3276	3203	3190	3190
query66	700	300	304	300
query67	16046	15909	15705	15705
query68	4139	602	580	580
query69	429	263	266	263
query70	1186	1045	1070	1045
query71	366	273	251	251
query72	6371	4232	4234	4232
query73	748	360	365	360
query74	10274	8943	9409	8943
query75	3331	2647	2681	2647
query76	2049	1137	1135	1135
query77	496	269	267	267
query78	10546	9789	9616	9616
query79	1551	581	600	581
query80	863	450	435	435
query81	489	217	221	217
query82	1106	88	86	86
query83	253	147	140	140
query84	291	81	80	80
query85	890	311	309	309
query86	323	300	272	272
query87	4470	4307	4229	4229
query88	3800	2407	2384	2384
query89	419	293	291	291
query90	2029	184	184	184
query91	138	107	106	106
query92	61	51	49	49
query93	1795	554	561	554
query94	741	269	281	269
query95	352	254	250	250
query96	602	284	278	278
query97	3343	3129	3176	3129
query98	214	203	196	196
query99	1597	1344	1318	1318
Total cold run time: 314632 ms
Total hot run time: 197013 ms

@doris-robot
Copy link

doris-robot commented Jun 24, 2025

ClickBench: Total hot run time: 30.36 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 42354be84338b01e3f41186b3cfad827722ca09f, data reload: false

query1	0.04	0.03	0.03
query2	0.07	0.03	0.02
query3	0.24	0.06	0.06
query4	1.62	0.10	0.11
query5	0.52	0.52	0.49
query6	1.13	0.72	0.73
query7	0.02	0.02	0.02
query8	0.04	0.03	0.03
query9	0.56	0.51	0.50
query10	0.56	0.55	0.57
query11	0.16	0.10	0.11
query12	0.15	0.11	0.12
query13	0.62	0.61	0.59
query14	0.77	0.80	0.79
query15	0.84	0.83	0.85
query16	0.38	0.38	0.39
query17	1.07	1.03	1.03
query18	0.23	0.22	0.21
query19	1.96	1.82	1.82
query20	0.02	0.01	0.01
query21	15.39	0.60	0.58
query22	2.49	1.50	2.33
query23	16.99	0.95	0.76
query24	3.10	1.50	1.62
query25	0.25	0.21	0.06
query26	0.48	0.14	0.13
query27	0.06	0.05	0.04
query28	9.46	0.55	0.51
query29	12.59	3.25	3.20
query30	0.25	0.06	0.06
query31	2.86	0.39	0.39
query32	3.22	0.46	0.46
query33	2.96	3.00	2.98
query34	16.92	4.50	4.54
query35	4.64	4.62	4.52
query36	0.68	0.48	0.50
query37	0.08	0.07	0.06
query38	0.05	0.03	0.03
query39	0.03	0.03	0.02
query40	0.17	0.13	0.12
query41	0.08	0.02	0.03
query42	0.04	0.02	0.02
query43	0.03	0.03	0.03
Total cold run time: 103.82 s
Total hot run time: 30.36 s

@seawinde
Copy link
Contributor Author

seawinde commented Jun 24, 2025

run buildall

@doris-robot
Copy link

doris-robot commented Jun 25, 2025

TPC-H: Total hot run time: 39503 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpch-tools
Tpch sf100 test result on commit 376d9e3484ef4e235c03f00d18da6688ef269c1c, data reload: false

------ Round 1 ----------------------------------
q1	17585	6686	6616	6616
q2	2070	173	169	169
q3	10572	1073	1186	1073
q4	10462	759	703	703
q5	7778	2834	2842	2834
q6	209	131	136	131
q7	969	625	607	607
q8	9337	1927	2038	1927
q9	6625	6366	6334	6334
q10	7001	2317	2280	2280
q11	460	266	263	263
q12	396	215	208	208
q13	17780	2979	3002	2979
q14	256	215	218	215
q15	516	457	478	457
q16	502	379	381	379
q17	983	595	573	573
q18	7158	6700	6550	6550
q19	1312	943	997	943
q20	474	208	193	193
q21	3953	3193	3071	3071
q22	1127	1022	998	998
Total cold run time: 107525 ms
Total hot run time: 39503 ms

----- Round 2, with runtime_filter_mode=off -----
q1	6593	6618	6598	6598
q2	326	227	241	227
q3	2922	2761	2777	2761
q4	2029	1783	1827	1783
q5	5745	5745	5712	5712
q6	210	128	128	128
q7	2199	1792	1818	1792
q8	3395	3544	3557	3544
q9	8903	8761	8913	8761
q10	3543	3505	3517	3505
q11	582	494	499	494
q12	810	626	631	626
q13	9162	3156	3226	3156
q14	312	264	284	264
q15	510	469	459	459
q16	490	451	441	441
q17	1829	1613	1596	1596
q18	8088	7823	7716	7716
q19	1694	1643	1567	1567
q20	2139	1796	1807	1796
q21	5236	4983	5094	4983
q22	1126	1067	1058	1058
Total cold run time: 67843 ms
Total hot run time: 58967 ms

@doris-robot
Copy link

doris-robot commented Jun 25, 2025

TPC-DS: Total hot run time: 198043 ms 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/tpcds-tools
TPC-DS sf100 test result on commit 376d9e3484ef4e235c03f00d18da6688ef269c1c, data reload: false

query1	1292	893	907	893
query2	6332	1942	1934	1934
query3	10911	4393	4543	4393
query4	60683	29686	23757	23757
query5	5148	463	457	457
query6	439	182	186	182
query7	5472	314	304	304
query8	314	232	238	232
query9	8728	2592	2588	2588
query10	432	289	274	274
query11	17476	15342	15778	15342
query12	160	109	110	109
query13	1484	454	433	433
query14	10431	7051	7497	7051
query15	204	193	181	181
query16	7118	512	478	478
query17	1159	599	589	589
query18	1862	326	338	326
query19	220	171	169	169
query20	121	114	113	113
query21	201	107	108	107
query22	4590	4638	4720	4638
query23	34832	33885	34081	33885
query24	6127	3005	2991	2991
query25	561	427	430	427
query26	664	177	174	174
query27	1804	359	372	359
query28	4475	2140	2136	2136
query29	727	444	452	444
query30	237	168	170	168
query31	966	832	840	832
query32	68	57	59	57
query33	415	301	302	301
query34	923	532	525	525
query35	877	733	735	733
query36	1096	958	944	944
query37	116	67	71	67
query38	4130	4087	3991	3991
query39	1539	1460	1478	1460
query40	211	105	101	101
query41	47	52	44	44
query42	115	104	104	104
query43	545	497	496	496
query44	1205	816	833	816
query45	187	169	167	167
query46	1148	736	728	728
query47	2038	1932	1965	1932
query48	440	344	340	340
query49	741	401	398	398
query50	839	431	433	431
query51	7328	7182	7180	7180
query52	108	96	93	93
query53	266	188	183	183
query54	575	471	480	471
query55	78	81	87	81
query56	269	255	272	255
query57	1304	1192	1220	1192
query58	226	216	215	215
query59	3284	3121	3064	3064
query60	292	257	262	257
query61	111	131	129	129
query62	808	701	709	701
query63	222	190	193	190
query64	1338	658	675	658
query65	3255	3198	3198	3198
query66	691	307	303	303
query67	15947	15668	15691	15668
query68	4024	603	577	577
query69	426	269	269	269
query70	1152	1111	1084	1084
query71	341	264	263	263
query72	6493	3918	3993	3918
query73	766	355	364	355
query74	10023	9013	9255	9013
query75	3387	2655	2679	2655
query76	1969	1150	1188	1150
query77	510	283	290	283
query78	10467	9620	9572	9572
query79	1676	603	607	603
query80	907	434	456	434
query81	497	226	230	226
query82	1266	98	91	91
query83	270	144	150	144
query84	287	90	81	81
query85	921	323	303	303
query86	318	293	305	293
query87	4431	4291	4225	4225
query88	3882	2407	2373	2373
query89	423	308	294	294
query90	2016	191	193	191
query91	144	113	108	108
query92	65	54	53	53
query93	1963	565	569	565
query94	743	301	287	287
query95	365	267	265	265
query96	619	279	293	279
query97	3333	3169	3161	3161
query98	213	211	192	192
query99	1575	1317	1297	1297
Total cold run time: 313424 ms
Total hot run time: 198043 ms

@doris-robot
Copy link

doris-robot commented Jun 25, 2025

ClickBench: Total hot run time: 30.1 s 
machine: 'aliyun_ecs.c7a.8xlarge_32C64G'
scripts: https://github.com/apache/doris/tree/master/tools/clickbench-tools
ClickBench test result on commit 376d9e3484ef4e235c03f00d18da6688ef269c1c, data reload: false

query1	0.04	0.03	0.04
query2	0.07	0.03	0.04
query3	0.23	0.06	0.06
query4	1.62	0.10	0.10
query5	0.51	0.50	0.49
query6	1.13	0.73	0.74
query7	0.02	0.01	0.01
query8	0.04	0.04	0.03
query9	0.57	0.50	0.50
query10	0.56	0.57	0.55
query11	0.15	0.10	0.10
query12	0.14	0.11	0.11
query13	0.60	0.60	0.60
query14	0.77	0.79	0.82
query15	0.84	0.84	0.83
query16	0.42	0.39	0.37
query17	1.06	1.01	1.00
query18	0.23	0.23	0.23
query19	1.96	1.86	1.82
query20	0.02	0.01	0.00
query21	15.39	0.58	0.58
query22	2.28	2.41	1.88
query23	16.92	1.04	0.94
query24	2.69	1.17	0.66
query25	0.23	0.05	0.13
query26	0.43	0.14	0.13
query27	0.05	0.05	0.03
query28	10.92	0.50	0.45
query29	12.58	3.25	3.22
query30	0.24	0.06	0.07
query31	2.86	0.39	0.39
query32	3.24	0.47	0.46
query33	2.98	3.06	3.05
query34	17.18	4.55	4.47
query35	4.56	4.57	4.58
query36	0.70	0.47	0.48
query37	0.09	0.06	0.06
query38	0.06	0.03	0.03
query39	0.03	0.02	0.02
query40	0.17	0.13	0.12
query41	0.07	0.02	0.02
query42	0.04	0.03	0.03
query43	0.03	0.03	0.03
Total cold run time: 104.72 s
Total hot run time: 30.1 s

@morrySnow morrySnow changed the title [opt](mtmv) optimize mtmv rewrite performance (#49514) branch-3.1: [opt](mtmv) optimize mtmv rewrite performance #49514 Jun 25, 2025
@morrySnow morrySnow merged commit 3fcde57 into apache:branch-3.1 Jun 25, 2025
20 of 21 checks passed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

@morrySnow morrySnow morrySnow approved these changes

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

4 participants

@seawinde @hello-stephen @doris-robot @morrySnow