G select * from t1, lateral(select f1 from t2 where t2.f2 = t1.f3 group by f1) as l  where t1.f1 = l.f1; (before rewrites) cluster0 Box0:Select cluster5 Box5:Select cluster4 Box4:Grouping cluster2 Box2:Select cluster3 Box3:BaseTable T2 cluster1 Box1:BaseTable T1 boxhead0 Distinct: Preserve 0: Q0.c0 as F1 1: Q0.c1 as F2 2: Q0.c2 as F3 3: Q4.c0 as F1 Q0 Q0(F) as T1 Q4 Q4(F) as L Q0->Q4 (Q0.c0) = (Q4.c0) Q1 Q1(F) as T2 Q0->Q1 (Q1.c1) = (Q0.c2) boxhead1 Distinct: Preserve 0: c0 as F1 1: c1 as F2 2: c2 as F3 Q0->boxhead1 boxhead5 Distinct: Preserve 0: Q3.c0 as F1 UNIQUE KEY [0] Q4->boxhead5 Q3 Q3(F) boxhead4 Distinct: Preserve 0: Q2.c0 as F1 GROUP Q2.c0 Ascending UNIQUE KEY [0] Q3->boxhead4 Q2 Q2(F) boxhead2 Distinct: Preserve 0: Q1.c0 as F1 1: Q1.c1 as F2 2: Q1.c2 as F3 Q2->boxhead2 boxhead3 Distinct: Preserve 0: c0 as F1 1: c1 as F2 2: c2 as F3 Q1->boxhead3