[SERVER-38021] 【query plans issue】inappropriate candidate plans are generated Created: 08/Nov/18  Updated: 08/Nov/18  Resolved: 08/Nov/18

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

Type: Bug Priority: Major - P3
Reporter: zhang mengzhi Assignee: Danny Hatcher (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-36393 Contained $or pushdown optimization c... Backlog
Operating System: ALL
Steps To Reproduce:

1、create index: 

{ f1:1 , f2: 1 }

2、aggregate explain:

db.data.aggregate([{
$match: {
    f1:1,
    $or: [
        { f2: 1 },
        {
           w: 1,
           f2: { $ne: 1 }
        }
    ]
}}], {
        explain: true
});

 when I add more elements to $or, the number of candidate plans is growing too, and it takes more time to evaluate them. 

Participants:

 Description   

when we update our mongodb from 3.4 to 3.6, some query plans take much more time than 3.4. some aggregation took 10 seconds in 3.6 while 100ms in 3.4.

explain(): "rejectedPlans" is empty in 3.4, but it contains one element in 3.6.

I found the variable "_candidates" in "multi_plan.cpp" are inappropriate. In this case, it may take much more time to evaluate these candidate plans.



 Comments   
Comment by Danny Hatcher (Inactive) [ 08/Nov/18 ]

Hello Zhang Mengzhi,

As Charlie pointed out, you have encountered an optimization introduced by SERVER-13732 so that the query planner generates more efficient plans. However, in order to do so, there is more overhead in the initial planning phase. SERVER-36393 will be used to handle further optimizations to the process so I recommend watching that ticket.

In the meantime, there are several options available to you.

  • Rewrite the query to ensure that there are not a large numbers of contained $or queries.
  • The more indexes that you have in a collection, the more plans that have to be evaluated. You may wish to analyze the current indexes in your collection to see if any of them are essentially unused as removing them shall speed things up.
  • You can create Index Filters for the query shape so the query planner doesn't evaluate unnecessary indexes. Please not that these filters do not persist after shutdown so will need to be recreated.
  • If you know of a specific index that you think will work best, you can specify a hint.

Because any changes to the code will originate in SERVER-36393, I will close this ticket as a duplicate.

Thank you,

Danny

Comment by zhang mengzhi [ 08/Nov/18 ]

for version 3.6, this expression will generate up to 64 (internalQueryPlannerMaxIndexedSolutions) candidate plans, but for version 3.4, it just generate 1 candidate plan.

db.data.aggregate([{
$match: {
    f1:1,
    $and: [
        {$or: [
            { f2: 1 },
            {
               w: 1,
               f2: { $ne: 1 }
            }
        ],}
        {$or: [...(same as above)]},
        {$or: [...(same as above)]},
        {$or: [...(same as above)]},
        {$or: [...(same as above)]},
        {$or: [...(same as above)]},
        {$or: [...(same as above)]},
        .....
    ]
}}], {
        explain: true
});

Comment by Charlie Swanson [ 08/Nov/18 ]

Upon first glance, this seems like a similar issue to SERVER-36393.

Comment by zhang mengzhi [ 08/Nov/18 ]

It Seems to be related to "index_tag.cpp"

Generated at Thu Feb 08 04:47:44 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.