-
Type:
Improvement
-
Resolution: Unresolved
-
Priority:
Major - P3
-
None
-
Affects Version/s: None
-
Component/s: None
-
None
-
Query Optimization
-
None
-
None
-
None
-
None
-
None
-
None
-
None
In the case of a small $limit, the non-join-optimized execution can terminate the processing of the entire query immediately once the $limit is reached. However, if join optimization elects to build any hash tables, those tables need to be built in their entirety, incurring a fixed cost regardless of the $limit.
To reproduce, run the following query against the tpch 0.1 dataset:
{">>>command": {"aggregate":"lineitem","pipeline":[{"$lookup":{"from":"orders","localField":"l_orderkey","foreignField":"o_orderkey","pipeline":[{"$match":{"$or":[{"o_clerk":"Clerk#000000654"},{"o_orderstatus":"O"},{"o_totalprice":{"$lte":201611.59}}]}},{"$match":{"$nor":[{"o_clerk":"Clerk#000000591"}]}}],"as":"orders"}},{"$unwind":"$orders"},{"$lookup":{"from":"customer","localField":"orders.o_custkey","foreignField":"c_custkey","pipeline":[{"$match":{"$or":[{"c_acctbal":{"$gt":3675.4}}]}}],"as":"customer"}},{"$unwind":"$customer"},{"$lookup":{"from":"supplier","localField":"customer.c_nationkey","foreignField":"s_nationkey","pipeline":[{"$match":{"$nor":[{"s_acctbal":{"$gt":5322.35}},{"s_name":"Supplier#000000390"},{"s_name":"Supplier#000000832"}]}}],"as":"supplier"}},{"$unwind":"$supplier"},{"$match":{"$or":[{"l_receiptdate":{"$gt":new Time("1996-07-09T00:00:00.000Z")}}]}},{"$limit":1000}]},
"winningPlan": [
-"FETCH: plan_stability_join_opt.lineitem ",
-" -> IXSCAN: plan_stability_join_opt.lineitem l_receiptdate_1 {"l_receiptdate":["(new Date(836870400000), new Date(9223372036854775807)]"]}"],
- "keys" : 2021,
- "docs" : 2021,
+"HJ s_nationkey = customer.c_nationkey",
+" -> [supplier] COLLSCAN: plan_stability_join_opt.supplier {"$nor":[{"s_name":{"$eq":"Supplier#000000390"}},{"s_name":{"$eq":"Supplier#000000832"}},{"s_acctbal":{"$gt":5322.35}}]} ",
+" -> [none] INLJ orders.o_orderkey = l_orderkey",
+" -> [none] HJ c_custkey = o_custkey",
+" -> [customer] COLLSCAN: plan_stability_join_opt.customer {"c_acctbal":{"$gt":3675.4}} ",
+" -> [orders] COLLSCAN: plan_stability_join_opt.orders {"$and":[{"$or":[{"o_clerk":{"$eq":"Clerk#000000654"}},{"o_orderstatus":{"$eq":"O"}},{"o_totalprice":{"$lte":201611.59}}]},{"o_clerk":{"$not":{"$eq":"Clerk#000000591"}}}]} ",
+" -> [none] FETCH: plan_stability_join_opt.lineitem {"l_receiptdate":{"$gt":"1996-07-09T00:00:00.000Z"}} ",
+" -> INDEX_PROBE_NODE: plan_stability_join_opt.lineitem l_orderkey_1"],
+ "keys" : 952,
+ "docs" : 63214,
"rows" : 1000},