[SERVER-64082] Consider usage of wildcard index for $lookup in SBE Created: 01/Mar/22  Updated: 28/Apr/22  Resolved: 28/Apr/22

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Nikita Lapkov (Inactive) Assignee: Mohammad Dashti (Inactive)
Resolution: Won't Fix Votes: 0
Labels: post-rc0
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Sprint: QE 2022-05-02
Participants:

 Description   

Currently, we do not use wildcard indexes because they do not contain entries for null values.

We could make the plan dynamic and use the index join strategy with wildcard index only if there is a non-null value. Otherwise, use another index or another strategy.



 Comments   
Comment by Eric Cox (Inactive) [ 27/Apr/22 ]

ethan.zhang@mongodb.com Given that we decided to not pushdown $lookup into SBE when wildcard indexes are used should we close this?

Comment by Ethan Zhang (Inactive) [ 19/Apr/22 ]

In our standup last week, we discussed this ticket and said we want to add a Genny benchmark to test classic vs. SBE $lookup when:

  • There is only a wildcard index on the foreign collection on the join key.
  • The local collection can have a varying percentage of NULL values.

In the classic engine, we replan the foreign side on every local document, so we know if the local join key is NULL or not. If the local key is not NULL, then we can generate a foreign collection access plan using the wildcard index. But if the local key is NULL, then we cannot use the wildcard index scan because wildcard indexes do not store NULL values.

If we vary the % of null values in the local collections, we may see different performance numbers. Because the classic engine can use the wildcard indexes for some local documents but SBE cannot use wildcard indexes at all. We want to understand how the performance is like in this case and if there is a break-even point

Generated at Thu Feb 08 05:59:28 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.