Skip to content

[feature](Nereids): Optimize Query Plan by Pulling Up Join with Common Child from Union (#42033) #43591

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 1 commit into from
Nov 12, 2024
Merged

[feature](Nereids): Optimize Query Plan by Pulling Up Join with Common Child from Union (#42033) #43591

morrySnow merged 1 commit into from
Nov 12, 2024

Conversation

@feiniaofeiafei
Copy link
Contributor

@feiniaofeiafei feiniaofeiafei commented Nov 11, 2024

cherry-pick #42033 to branch-3.0

@doris-robot
Copy link

doris-robot commented Nov 11, 2024

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?

@feiniaofeiafei
Copy link
Contributor Author

feiniaofeiafei commented Nov 11, 2024

run buildall

@feiniaofeiafei
[feature](Nereids): Optimize Query Plan by Pulling Up Join with Commo…
80c6c52 
…n Child from Union (apache#42033)

This pr adds rewrite rule PullUpJoinFromUnion, supports pull up join
from union all separately, separates this rule from apache#28682, deletes the
original PullUpJoinFromUnionAll rule.
@feiniaofeiafei
Copy link
Contributor Author

feiniaofeiafei commented Nov 11, 2024

run buildall

@doris-robot
Copy link

doris-robot commented Nov 11, 2024

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

------ Round 1 ----------------------------------
q1	17570	7387	7275	7275
q2	2069	198	175	175
q3	10491	1117	1146	1117
q4	10224	768	741	741
q5	7739	2836	2971	2836
q6	233	146	147	146
q7	966	613	625	613
q8	9394	1975	1984	1975
q9	7937	6397	6376	6376
q10	7023	2317	2299	2299
q11	446	260	260	260
q12	492	214	207	207
q13	17773	2977	2994	2977
q14	248	208	220	208
q15	575	530	523	523
q16	678	599	591	591
q17	980	543	559	543
q18	7338	6646	6760	6646
q19	1386	1057	970	970
q20	467	198	196	196
q21	3859	3121	3315	3121
q22	1061	982	988	982
Total cold run time: 108949 ms
Total hot run time: 40777 ms

----- Round 2, with runtime_filter_mode=off -----
q1	7244	7239	7197	7197
q2	400	314	308	308
q3	2859	2659	2729	2659
q4	1898	1646	1684	1646
q5	5377	5480	5468	5468
q6	212	135	139	135
q7	2096	1642	1685	1642
q8	3246	3390	3405	3390
q9	8553	8522	8514	8514
q10	3470	3356	3395	3356
q11	574	490	486	486
q12	754	554	554	554
q13	16920	2982	2974	2974
q14	303	260	254	254
q15	584	548	537	537
q16	729	640	647	640
q17	1812	1555	1551	1551
q18	7591	7446	7564	7446
q19	1622	1597	1472	1472
q20	2027	1796	1791	1791
q21	5533	5110	4965	4965
q22	1081	985	989	985
Total cold run time: 74885 ms
Total hot run time: 57970 ms

@doris-robot
Copy link

doris-robot commented Nov 11, 2024

TPC-DS: Total hot run time: 190519 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 80c6c52a2e3d7cce545adf8a4502c4e28aee0f57, data reload: false

query1	966	381	370	370
query2	6428	2112	1990	1990
query3	6668	211	219	211
query4	34004	23478	23447	23447
query5	4175	445	418	418
query6	265	180	168	168
query7	4612	301	317	301
query8	276	219	234	219
query9	9477	2683	2648	2648
query10	479	271	272	271
query11	18122	15198	15274	15198
query12	152	105	101	101
query13	1650	417	391	391
query14	9911	6820	6251	6251
query15	220	180	175	175
query16	7758	501	469	469
query17	1584	565	552	552
query18	2268	601	613	601
query19	224	181	175	175
query20	114	111	110	110
query21	205	102	107	102
query22	4333	4307	4181	4181
query23	34978	34760	34167	34167
query24	12859	3425	3371	3371
query25	732	401	400	400
query26	1878	180	186	180
query27	2967	295	297	295
query28	8396	2498	2460	2460
query29	1133	455	436	436
query30	454	297	304	297
query31	1031	781	780	780
query32	97	57	58	57
query33	782	284	273	273
query34	1001	495	513	495
query35	850	730	739	730
query36	1103	928	940	928
query37	264	71	79	71
query38	3980	3846	3790	3790
query39	1486	1419	1428	1419
query40	285	99	99	99
query41	55	52	52	52
query42	110	99	97	97
query43	529	485	479	479
query44	1229	796	784	784
query45	187	171	168	168
query46	1127	710	715	710
query47	1902	1816	1809	1809
query48	462	381	366	366
query49	1223	386	379	379
query50	809	407	397	397
query51	7171	7003	7120	7003
query52	106	92	95	92
query53	262	189	181	181
query54	1140	450	443	443
query55	77	75	77	75
query56	256	245	256	245
query57	1205	1101	1107	1101
query58	226	212	212	212
query59	3283	2988	2893	2893
query60	303	237	241	237
query61	108	108	106	106
query62	835	651	659	651
query63	215	185	181	181
query64	5203	642	611	611
query65	3272	3147	3171	3147
query66	1181	302	306	302
query67	16259	15342	15214	15214
query68	5765	572	583	572
query69	396	253	247	247
query70	1119	1153	1052	1052
query71	408	246	245	245
query72	6608	3874	3933	3874
query73	782	337	336	336
query74	9961	8934	8934	8934
query75	3397	2605	2643	2605
query76	3594	1071	962	962
query77	506	260	262	260
query78	11037	9766	9540	9540
query79	9295	596	595	595
query80	1909	410	417	410
query81	571	242	236	236
query82	1359	120	120	120
query83	306	160	159	159
query84	294	79	81	79
query85	2351	303	285	285
query86	489	308	300	300
query87	4608	4277	4154	4154
query88	5534	2353	2378	2353
query89	547	300	295	295
query90	2249	185	180	180
query91	176	144	143	143
query92	57	50	47	47
query93	7609	536	531	531
query94	1172	284	281	281
query95	340	251	247	247
query96	619	281	283	281
query97	3370	3182	3192	3182
query98	209	197	197	197
query99	1648	1285	1298	1285
Total cold run time: 325187 ms
Total hot run time: 190519 ms

@feiniaofeiafei
Copy link
Contributor Author

feiniaofeiafei commented Nov 11, 2024

run external

@morrySnow morrySnow merged commit 1d85a71 into apache:branch-3.0 Nov 12, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

No reviews

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

3 participants

@feiniaofeiafei @doris-robot @morrySnow