Skip to content
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

[Enhancement](mtmv) Support read sync materialized view in async materialized view #37396

Open
wants to merge 4 commits into
base: master
Choose a base branch
from
Open

[Enhancement](mtmv) Support read sync materialized view in async materialized view #37396

wants to merge 4 commits into from
+83 −0

Conversation

liutang123
Copy link
Contributor

Identify the column in async materialized view definition. If it selected a column from a sync materialized view, find the real column from the olap table and check if it is the partition column of the olap table.

Proposed changes

Issue Number: close #37394
@liutang123
Copy link
Contributor Author

Will add regression tests after reviewed.
@liutang123 liutang123 changed the title [Enhancement] (mtmv) Support read sync materialized view in async materialized view Jul 7, 2024
@liutang123
Copy link
Contributor Author

run buildall
@liutang123
Copy link
Contributor Author

@morrySnow Hi, Do you have time to see this PR an give some suggestion?
@morrySnow
Copy link
Contributor

Could you give a more specific example and put it into the regression test?
@liutang123
Copy link
Contributor Author

run buildall
@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17613	4390	4363	4363
q2	2017	193	196	193
q3	10446	1177	1182	1177
q4	10193	827	700	700
q5	7539	2681	2639	2639
q6	224	142	142	142
q7	953	611	614	611
q8	9217	2090	2060	2060
q9	9031	6584	6563	6563
q10	8896	3825	3762	3762
q11	441	233	240	233
q12	466	238	237	237
q13	17817	3015	3017	3015
q14	283	232	234	232
q15	513	484	484	484
q16	506	402	380	380
q17	970	636	717	636
q18	8111	7363	7402	7363
q19	5211	1393	1358	1358
q20	652	323	316	316
q21	5016	3942	3370	3370
q22	355	290	292	290
Total cold run time: 116470 ms
Total hot run time: 40124 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4408	4302	4295	4295
q2	382	291	268	268
q3	3006	2883	2933	2883
q4	2022	1703	1674	1674
q5	5638	5567	5462	5462
q6	282	138	137	137
q7	2229	1931	1860	1860
q8	3302	3461	3433	3433
q9	8821	8773	8928	8773
q10	4100	3887	3761	3761
q11	584	513	511	511
q12	825	640	637	637
q13	17079	3168	3171	3168
q14	317	297	290	290
q15	552	491	486	486
q16	503	447	450	447
q17	1826	1528	1547	1528
q18	8055	7918	7920	7918
q19	2521	1596	1592	1592
q20	2109	1885	1853	1853
q21	5133	4964	4677	4677
q22	610	498	492	492
Total cold run time: 74304 ms
Total hot run time: 56145 ms
@doris-robot
Copy link

TPC-DS: Total hot run time: 173792 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 f4fa556f5151fbc34e9393447a5ad02da95c8354, data reload: false

query1	924	369	362	362
query2	6453	1893	1817	1817
query3	6687	204	216	204
query4	27749	17605	17341	17341
query5	3646	509	504	504
query6	260	170	166	166
query7	4841	298	304	298
query8	249	194	197	194
query9	8435	2363	2347	2347
query10	443	300	276	276
query11	12130	10257	10082	10082
query12	115	91	86	86
query13	1636	378	362	362
query14	10242	7841	7600	7600
query15	217	170	172	170
query16	7743	331	321	321
query17	1633	611	519	519
query18	1944	281	276	276
query19	202	149	154	149
query20	92	84	80	80
query21	204	130	125	125
query22	4249	4080	3968	3968
query23	34078	33872	33542	33542
query24	10908	2946	2852	2852
query25	589	390	395	390
query26	706	156	149	149
query27	2199	279	287	279
query28	6046	2037	2027	2027
query29	883	659	631	631
query30	256	161	155	155
query31	957	756	756	756
query32	103	54	54	54
query33	662	300	297	297
query34	899	503	519	503
query35	690	596	587	587
query36	1139	1003	974	974
query37	157	95	85	85
query38	3003	2849	2802	2802
query39	876	858	833	833
query40	207	124	122	122
query41	48	45	47	45
query42	116	104	106	104
query43	494	460	458	458
query44	1085	731	726	726
query45	188	165	166	165
query46	1092	735	732	732
query47	1842	1786	1759	1759
query48	378	295	292	292
query49	836	418	419	418
query50	801	408	405	405
query51	6925	6755	6827	6755
query52	110	95	97	95
query53	356	288	281	281
query54	925	442	451	442
query55	74	73	73	73
query56	301	271	272	271
query57	1137	1068	1041	1041
query58	250	278	271	271
query59	2859	2611	2540	2540
query60	303	305	286	286
query61	95	96	97	96
query62	775	639	667	639
query63	314	297	293	293
query64	9149	2224	7527	2224
query65	3140	3162	3121	3121
query66	746	330	333	330
query67	15325	14945	14995	14945
query68	4510	528	525	525
query69	591	456	352	352
query70	1171	1114	1102	1102
query71	392	293	289	289
query72	7437	5391	5525	5391
query73	767	326	329	326
query74	5924	5666	5512	5512
query75	3355	2693	2738	2693
query76	2428	992	931	931
query77	620	319	305	305
query78	11167	9177	8944	8944
query79	3386	523	521	521
query80	1685	503	500	500
query81	602	226	225	225
query82	802	143	134	134
query83	315	191	172	172
query84	275	87	99	87
query85	975	312	303	303
query86	474	316	317	316
query87	3251	3128	3103	3103
query88	4450	2477	2477	2477
query89	494	397	386	386
query90	1806	203	198	198
query91	131	105	102	102
query92	64	51	52	51
query93	5092	513	499	499
query94	1187	213	217	213
query95	409	323	325	323
query96	660	275	272	272
query97	3210	3017	2993	2993
query98	229	201	204	201
query99	1596	1269	1254	1254
Total cold run time: 283571 ms
Total hot run time: 173792 ms
@doris-robot
Copy link

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

query1	0.04	0.03	0.03
query2	0.07	0.04	0.04
query3	0.23	0.05	0.04
query4	1.68	0.07	0.07
query5	0.49	0.47	0.50
query6	1.14	0.73	0.72
query7	0.02	0.02	0.01
query8	0.05	0.04	0.04
query9	0.55	0.48	0.47
query10	0.55	0.53	0.54
query11	0.15	0.11	0.11
query12	0.15	0.12	0.12
query13	0.59	0.59	0.59
query14	0.76	0.78	0.80
query15	0.86	0.81	0.81
query16	0.35	0.37	0.35
query17	1.01	1.03	1.02
query18	0.23	0.22	0.22
query19	1.79	1.73	1.77
query20	0.01	0.01	0.00
query21	15.41	0.76	0.65
query22	3.47	7.65	2.04
query23	18.30	1.43	1.36
query24	2.14	0.22	0.24
query25	0.16	0.09	0.08
query26	0.29	0.22	0.22
query27	0.45	0.24	0.23
query28	13.19	1.02	1.00
query29	12.60	3.30	3.27
query30	0.25	0.06	0.06
query31	2.87	0.41	0.38
query32	3.27	0.48	0.47
query33	2.93	2.81	3.00
query34	16.80	4.38	4.44
query35	4.37	4.42	4.43
query36	0.66	0.46	0.49
query37	0.18	0.16	0.15
query38	0.15	0.15	0.15
query39	0.05	0.04	0.04
query40	0.16	0.12	0.13
query41	0.09	0.04	0.04
query42	0.06	0.05	0.05
query43	0.05	0.04	0.04
Total cold run time: 108.62 s
Total hot run time: 30.79 s
@liutang123
Copy link
Contributor Author

run buildall
@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17612	4345	4271	4271
q2	2013	191	194	191
q3	10453	1240	1129	1129
q4	10199	909	889	889
q5	7615	2733	2644	2644
q6	227	140	140	140
q7	961	603	602	602
q8	9219	2111	2111	2111
q9	8780	6581	6600	6581
q10	8838	3817	3779	3779
q11	445	237	242	237
q12	413	235	227	227
q13	17768	2984	3001	2984
q14	277	238	235	235
q15	525	496	496	496
q16	485	385	375	375
q17	970	768	732	732
q18	8137	7429	7456	7429
q19	7295	1433	1389	1389
q20	697	327	328	327
q21	5016	3215	3253	3215
q22	359	284	287	284
Total cold run time: 118304 ms
Total hot run time: 40267 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4455	4242	4233	4233
q2	378	266	274	266
q3	3040	2889	2877	2877
q4	2002	1687	1719	1687
q5	5615	5567	5536	5536
q6	230	138	143	138
q7	2204	1896	1873	1873
q8	3258	3448	3493	3448
q9	8782	8765	8989	8765
q10	4104	3980	3784	3784
q11	594	495	496	495
q12	835	619	644	619
q13	15957	3205	3208	3205
q14	323	288	279	279
q15	530	485	502	485
q16	479	447	449	447
q17	1851	1549	1521	1521
q18	8013	7913	7899	7899
q19	1761	1672	1661	1661
q20	2540	1873	1897	1873
q21	5157	4951	4733	4733
q22	582	523	510	510
Total cold run time: 72690 ms
Total hot run time: 56334 ms
@doris-robot
Copy link

TPC-DS: Total hot run time: 174310 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 e0a82662b8ef9b4192ec9a2e7f7a3fe340c4aa9e, data reload: false

query1	922	379	364	364
query2	6532	1803	1803	1803
query3	6636	219	217	217
query4	27613	17358	17221	17221
query5	3666	497	488	488
query6	287	176	170	170
query7	4590	290	291	290
query8	248	198	204	198
query9	8565	2382	2367	2367
query10	444	300	273	273
query11	11348	10096	10080	10080
query12	116	89	88	88
query13	1635	376	382	376
query14	10339	7884	7786	7786
query15	216	168	164	164
query16	7751	314	316	314
query17	1798	565	532	532
query18	1888	284	273	273
query19	196	148	145	145
query20	88	80	81	80
query21	207	130	123	123
query22	4270	4091	4176	4091
query23	34050	33800	33698	33698
query24	11236	3009	2931	2931
query25	596	402	404	402
query26	703	148	149	148
query27	2242	277	273	273
query28	6164	2048	2027	2027
query29	937	634	633	633
query30	252	150	183	150
query31	1014	760	766	760
query32	96	55	57	55
query33	759	303	286	286
query34	988	505	507	505
query35	687	595	626	595
query36	1110	987	1008	987
query37	145	85	93	85
query38	2983	2862	2841	2841
query39	928	858	858	858
query40	205	123	117	117
query41	43	43	44	43
query42	116	99	100	99
query43	497	454	471	454
query44	1253	739	738	738
query45	189	162	189	162
query46	1094	726	695	695
query47	1855	1754	1784	1754
query48	375	283	285	283
query49	841	413	415	413
query50	804	386	392	386
query51	6939	6829	6815	6815
query52	100	99	93	93
query53	357	299	291	291
query54	864	442	445	442
query55	74	74	74	74
query56	288	269	283	269
query57	1113	1046	1062	1046
query58	261	240	256	240
query59	2774	2739	2582	2582
query60	294	295	278	278
query61	99	95	97	95
query62	794	635	704	635
query63	321	295	288	288
query64	9182	2246	1687	1687
query65	3161	3095	3116	3095
query66	762	329	332	329
query67	15515	15108	15066	15066
query68	4529	524	523	523
query69	474	324	335	324
query70	1189	1097	1093	1093
query71	373	285	285	285
query72	7177	5834	5653	5653
query73	740	325	325	325
query74	6269	5658	5724	5658
query75	3452	2828	2711	2711
query76	2286	939	935	935
query77	461	368	321	321
query78	11035	9003	9130	9003
query79	2784	521	524	521
query80	1994	486	495	486
query81	597	222	224	222
query82	858	144	138	138
query83	285	170	169	169
query84	277	89	91	89
query85	1604	315	303	303
query86	477	317	309	309
query87	3292	3116	3124	3116
query88	4738	2448	2483	2448
query89	497	382	396	382
query90	1756	195	198	195
query91	130	102	99	99
query92	63	50	51	50
query93	4662	509	507	507
query94	1144	220	216	216
query95	406	322	329	322
query96	617	277	272	272
query97	3208	3047	3113	3047
query98	216	270	191	191
query99	1566	1251	1261	1251
Total cold run time: 283571 ms
Total hot run time: 174310 ms
@doris-robot
Copy link

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

query1	0.04	0.04	0.03
query2	0.09	0.04	0.04
query3	0.22	0.04	0.05
query4	1.68	0.08	0.08
query5	0.49	0.47	0.49
query6	1.13	0.72	0.73
query7	0.02	0.01	0.02
query8	0.05	0.04	0.04
query9	0.54	0.48	0.47
query10	0.53	0.54	0.54
query11	0.15	0.11	0.11
query12	0.14	0.12	0.12
query13	0.58	0.59	0.59
query14	0.76	0.79	0.77
query15	0.86	0.81	0.82
query16	0.35	0.35	0.37
query17	1.02	1.04	1.04
query18	0.22	0.22	0.22
query19	1.92	1.75	1.74
query20	0.01	0.02	0.01
query21	15.40	0.75	0.66
query22	4.51	7.10	1.90
query23	18.33	1.40	1.32
query24	2.06	0.26	0.22
query25	0.14	0.09	0.09
query26	0.28	0.21	0.20
query27	0.46	0.23	0.23
query28	13.20	1.00	0.99
query29	12.59	3.35	3.32
query30	0.25	0.06	0.05
query31	2.87	0.39	0.39
query32	3.28	0.47	0.47
query33	2.89	2.93	2.95
query34	17.20	4.34	4.37
query35	4.41	4.41	4.36
query36	0.65	0.47	0.49
query37	0.19	0.16	0.15
query38	0.15	0.14	0.14
query39	0.04	0.04	0.03
query40	0.15	0.12	0.12
query41	0.09	0.05	0.05
query42	0.06	0.05	0.05
query43	0.05	0.04	0.04
Total cold run time: 110.05 s
Total hot run time: 30.72 s
@liutang123
Copy link
Contributor Author

run buildall
@doris-robot
Copy link

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

------ Round 1 ----------------------------------
q1	17618	4526	4257	4257
q2	2039	202	187	187
q3	10796	1214	1175	1175
q4	10526	805	825	805
q5	7597	2713	2722	2713
q6	221	136	138	136
q7	970	605	606	605
q8	9536	2133	2084	2084
q9	8745	6665	6690	6665
q10	9112	3746	3730	3730
q11	470	235	237	235
q12	394	226	222	222
q13	17773	2988	2947	2947
q14	277	242	239	239
q15	535	479	483	479
q16	495	384	373	373
q17	951	630	717	630
q18	8062	7435	7382	7382
q19	3553	1441	1337	1337
q20	698	318	343	318
q21	4876	3122	3194	3122
q22	353	285	287	285
Total cold run time: 115597 ms
Total hot run time: 39926 ms

----- Round 2, with runtime_filter_mode=off -----
q1	4359	4285	4185	4185
q2	377	260	262	260
q3	2985	2752	2700	2700
q4	1886	1614	1609	1609
q5	5288	5266	5310	5266
q6	216	129	129	129
q7	2079	1736	1693	1693
q8	3192	3327	3311	3311
q9	8379	8370	8358	8358
q10	3841	3701	3676	3676
q11	588	487	487	487
q12	791	559	596	559
q13	17361	2957	2960	2957
q14	304	272	275	272
q15	524	479	463	463
q16	482	413	419	413
q17	1764	1508	1449	1449
q18	7575	7556	7318	7318
q19	2928	1535	1375	1375
q20	1982	1780	1792	1780
q21	4874	4729	4659	4659
q22	575	500	518	500
Total cold run time: 72350 ms
Total hot run time: 53419 ms
@doris-robot
Copy link

TPC-DS: Total hot run time: 173687 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 d8e5f110db8946c3e6909a97c2e771daeae21ccd, data reload: false

query1	917	368	365	365
query2	6467	1922	1846	1846
query3	6665	204	213	204
query4	28670	17745	17345	17345
query5	4213	488	486	486
query6	270	167	183	167
query7	4590	279	281	279
query8	249	220	203	203
query9	8651	2373	2344	2344
query10	446	298	278	278
query11	11786	10336	10135	10135
query12	135	86	86	86
query13	1636	369	364	364
query14	10130	7725	7588	7588
query15	222	166	170	166
query16	7842	317	316	316
query17	1809	587	541	541
query18	1976	293	286	286
query19	207	154	154	154
query20	92	85	87	85
query21	211	130	126	126
query22	4382	4063	4104	4063
query23	33826	33088	33105	33088
query24	11114	2873	2897	2873
query25	660	385	392	385
query26	1539	154	150	150
query27	2915	266	268	266
query28	7607	1959	1944	1944
query29	1030	645	628	628
query30	288	151	149	149
query31	943	764	763	763
query32	97	53	57	53
query33	801	326	316	316
query34	903	479	474	474
query35	737	600	590	590
query36	1126	949	958	949
query37	149	85	81	81
query38	2939	2765	2778	2765
query39	873	823	857	823
query40	278	123	122	122
query41	50	48	47	47
query42	124	97	103	97
query43	507	462	460	460
query44	1158	735	725	725
query45	202	169	167	167
query46	1073	731	734	731
query47	1913	1799	1815	1799
query48	359	296	297	296
query49	1190	428	419	419
query50	779	395	384	384
query51	6836	6798	6786	6786
query52	105	95	96	95
query53	369	290	280	280
query54	1062	451	464	451
query55	76	74	74	74
query56	325	292	289	289
query57	1174	1073	1095	1073
query58	255	383	246	246
query59	2954	2687	2576	2576
query60	317	279	288	279
query61	96	92	94	92
query62	824	652	652	652
query63	323	281	282	281
query64	10473	2216	3801	2216
query65	3127	3085	3082	3082
query66	1377	330	331	330
query67	15732	14881	15368	14881
query68	8314	541	536	536
query69	754	463	357	357
query70	1193	1068	1143	1068
query71	538	275	271	271
query72	8731	5354	5600	5354
query73	820	326	320	320
query74	6158	5777	5638	5638
query75	5064	2690	2729	2690
query76	4907	999	903	903
query77	777	298	310	298
query78	10560	9395	10003	9395
query79	9491	521	513	513
query80	2020	471	470	470
query81	595	227	234	227
query82	760	131	134	131
query83	287	165	162	162
query84	276	92	86	86
query85	1377	317	337	317
query86	409	286	305	286
query87	3354	3193	3157	3157
query88	4778	2429	2434	2429
query89	536	368	383	368
query90	2072	194	193	193
query91	134	103	103	103
query92	68	53	49	49
query93	5891	488	485	485
query94	1021	211	208	208
query95	406	307	310	307
query96	606	280	267	267
query97	3174	3034	3003	3003
query98	215	198	203	198
query99	1559	1298	1244	1244
Total cold run time: 309016 ms
Total hot run time: 173687 ms
@doris-robot
Copy link

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

query1	0.04	0.03	0.03
query2	0.08	0.04	0.04
query3	0.22	0.04	0.05
query4	1.69	0.06	0.06
query5	0.50	0.50	0.46
query6	1.14	0.74	0.73
query7	0.02	0.02	0.02
query8	0.06	0.05	0.04
query9	0.54	0.49	0.49
query10	0.55	0.54	0.54
query11	0.15	0.11	0.11
query12	0.14	0.12	0.12
query13	0.58	0.58	0.58
query14	0.76	0.78	0.76
query15	0.85	0.81	0.80
query16	0.36	0.36	0.37
query17	0.94	0.97	1.03
query18	0.22	0.21	0.22
query19	1.78	1.85	1.70
query20	0.01	0.00	0.01
query21	15.43	0.74	0.67
query22	4.25	6.42	2.75
query23	18.24	1.34	1.24
query24	2.15	0.24	0.21
query25	0.16	0.08	0.09
query26	0.30	0.21	0.21
query27	0.45	0.23	0.23
query28	13.27	1.02	0.99
query29	12.59	3.28	3.30
query30	0.25	0.06	0.05
query31	2.87	0.39	0.38
query32	3.29	0.48	0.46
query33	2.86	2.88	2.93
query34	17.12	4.43	4.38
query35	4.36	4.44	4.41
query36	0.64	0.48	0.46
query37	0.20	0.16	0.15
query38	0.16	0.15	0.15
query39	0.05	0.03	0.04
query40	0.15	0.13	0.12
query41	0.10	0.06	0.05
query42	0.06	0.05	0.05
query43	0.04	0.04	0.03
Total cold run time: 109.62 s
Total hot run time: 31.32 s
@liutang123
Copy link
Contributor Author

run buildall
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
3 participants
@liutang123 @morrySnow @doris-robot