[SERVER-16341] Query planner chooses a table scan even an index exists Created: 27/Nov/14  Updated: 11/Mar/15  Resolved: 11/Mar/15

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.6.1
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Daniel Breitlauch Assignee: Geert Bosch
Resolution: Cannot Reproduce Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

Hard because it depends on the load an hardware how the query planner decides.
Situation:
Documents:

{
    "_id" : 1,
    "a" : [ 
        	{ "b" : 1 }
	],
	"c" : [ 1, 2, 3],
	"l" : 1
}

update operation:

{
	query: { "a.b" : 1, "c" : 1 } 
	update: { $pull: { "c" : 1 }, $set: { "l" : 1 } }
}

index:

{
    "a.b" : 1,
    "x" : -1,
    "_id" : 1
}

Participants:

 Description   

We are trying to enable --notablescan, however there is one update operation that generates a table scan even when a suitable index exists.
And since hinting does not exists for updates we tried index filter.
Sadly the planner always includes the table scan solution even when it has other (faster) options and --notablescan is activated.

There are multiple mitigating solutions like implement hints for updates or having the option to exclude table scans in index filters but this seams really like a bug in the query planner.
The query planner should not try the table scan if it is not allowed.
Or it should catch the table scan failure and take another solution.

Are there other mechanism to prevent table scans?



 Comments   
Comment by Geert Bosch [ 11/Mar/15 ]

If neither of us can reproduce this problem outside of your production setup, and we cannot find any issues in our code that would lead to the problem you are seeing by inspection, I am not sure we can make progress on this ticket. Please let us know if you have a self-contained reproducer or can reproduce with version 3.0, but we will close this ticket until that time.

Comment by Daniel Breitlauch [ 04/Mar/15 ]

We can not go to Mongo 3.0 in production systems now.

Comment by Geert Bosch [ 23/Feb/15 ]

Hi Daniel,

Actually, MongoDB 3.0 does have explain for updates. As we're not able to reproduce the problem you're encountering, I'd like to suggest that you see if the problem reproduces with the current release candidate of 3.0. If it does, you'd have explain output for the update that should make it possible to pinpoint the origin of your problem and find a solution that also might work for 2.6. If it doesn't reproduce with 3.0, you'd at least know that that would be a possible path forward.

-Geert

Comment by Daniel Breitlauch [ 23/Feb/15 ]

Hi Geert,

the message from 12 Dec contains the explain of the query.
As I said before, the query does not produce a table scan but the same query used in an update does.
Having an explain for the update would be very helpful.
Besides that, the message from 11 Dec contains the exception + stack trace.

I do not know how I could help more.
Daniel

Comment by Geert Bosch [ 23/Feb/15 ]

The explain part from your comment on dec 12 does not appear to include any collection scans. It would be really helpful if you could include the exact command you are giving as well as the resulting error messages / log output. As we cannot reproduce this, and we don't have concrete evidence (in the form of explain output) that the query planner was considering a collection scan, there is not much we can do to resolve this ticket without more information.

Regards,
-Geert

Comment by Daniel Breitlauch [ 11/Feb/15 ]

The query-, document- and index-structure is as described above (steps to reproduce).

Comment by Geert Bosch [ 09/Feb/15 ]

Hi Daniel,

We have reviewed the query planner logic, and it should never select a collection scan if an index scan is possible. Could you give your exact query command as well as the list of indexes on the (sharded) collection that you are querying?

Thanks in advance,
-Geert

Comment by Daniel Breitlauch [ 15/Dec/14 ]

Good idea. This would explain the issue.
Sadly the query/update is querying for real values ("a.b" : 1234)

I am looking forward to see your script.

Comment by David Storch [ 12/Dec/14 ]

Hi breitlauch,

Thanks a lot for providing the additional information. I have one last theory of what might be going on; if I'm wrong, then we can try to put together a script on our end that you can run which will dump info about the state of your cluster. We might be able to reach a diagnosis by applying this blunter hammer.

Getting to my theory: is it possible that any of the failing updates are somehow querying for a.b equal to null? I noticed that your indices are sparse, and a sparse index cannot be used to answer an equality test for null. For example, if I start a mongod server with --notable scan and then run the following:

> db.foo.ensureIndex({"a.b": 1, "x": -1, "_id": 1}, {sparse: true})
{
	"createdCollectionAutomatically" : true,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.update({"a.b": null}, {$set: {lala: "ma"}}, {upsert: false, multi: true})
WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"nModified" : 0,
	"writeError" : {
		"code" : 2,
		"errmsg" : "error processing query: ns=test.foo limit=0 skip=0\nTree: a.b == null\nSort: {}\nProj: {}\n No query solutions"
	}
})

I am going to reassign this case to my colleague scotthernandez, as I will be out of the office next week.

Best,
Dave

Comment by Daniel Breitlauch [ 12/Dec/14 ]

The explain(true) for the query part of the update operation.

{
    "clusteredType" : "ParallelSort",
    "shards" : {
        "repset1" : [ 
            {
                "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                "isMultiKey" : true,
                "n" : 0,
                "nscannedObjects" : 0,
                "nscanned" : 0,
                "nscannedObjectsAllPlans" : 0,
                "nscannedAllPlans" : 0,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {
                    "a.b" : [ 
                        [ 
                            2035284135, 
                            2035284135
                        ]
                    ],
                    "x" : [ 
                        [ 
                            {
                                "$maxElement" : 1
                            }, 
                            {
                                "$minElement" : 1
                            }
                        ]
                    ],
                    "_id" : [ 
                        [ 
                            {
                                "$minElement" : 1
                            }, 
                            {
                                "$maxElement" : 1
                            }
                        ]
                    ]
                },
                "allPlans" : [ 
                    {
                        "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                        "isMultiKey" : true,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                            "a.b" : [ 
                                [ 
                                    2035284135, 
                                    2035284135
                                ]
                            ],
                            "x" : [ 
                                [ 
                                    {
                                        "$maxElement" : 1
                                    }, 
                                    {
                                        "$minElement" : 1
                                    }
                                ]
                            ],
                            "_id" : [ 
                                [ 
                                    {
                                        "$minElement" : 1
                                    }, 
                                    {
                                        "$maxElement" : 1
                                    }
                                ]
                            ]
                        }
                    }
                ],
                "server" : "xxx",
                "filterSet" : false,
                "stats" : {
                    "type" : "PROJECTION",
                    "works" : 1,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 0,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "isEOF" : 1,
                    "children" : [ 
                        {
                            "type" : "KEEP_MUTATIONS",
                            "works" : 1,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 0,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "children" : [ 
                                {
                                    "type" : "SHARDING_FILTER",
                                    "works" : 1,
                                    "yields" : 0,
                                    "unyields" : 0,
                                    "invalidates" : 0,
                                    "advanced" : 0,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "isEOF" : 1,
                                    "chunkSkips" : 0,
                                    "children" : [ 
                                        {
                                            "type" : "FETCH",
                                            "works" : 1,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 0,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "alreadyHasObj" : 0,
                                            "forcedFetches" : 0,
                                            "matchTested" : 0,
                                            "children" : [ 
                                                {
                                                    "type" : "IXSCAN",
                                                    "works" : 1,
                                                    "yields" : 0,
                                                    "unyields" : 0,
                                                    "invalidates" : 0,
                                                    "advanced" : 0,
                                                    "needTime" : 0,
                                                    "needFetch" : 0,
                                                    "isEOF" : 1,
                                                    "keyPattern" : "{ a.b: 1.0, x: -1.0, _id: 1.0 }",
                                                    "isMultiKey" : 1,
                                                    "boundsVerbose" : "field #0['a.b']: [2035284135.0, 2035284135.0], field #1['x']: [MaxKey, MinKey], field #2['_id']: [MinKey, MaxKey]",
                                                    "yieldMovedCursor" : 0,
                                                    "dupsTested" : 0,
                                                    "dupsDropped" : 0,
                                                    "seenInvalidated" : 0,
                                                    "matchTested" : 0,
                                                    "keysExamined" : 0,
                                                    "children" : []
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "repset2" : [ 
            {
                "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                "isMultiKey" : true,
                "n" : 1,
                "nscannedObjects" : 1,
                "nscanned" : 1,
                "nscannedObjectsAllPlans" : 1,
                "nscannedAllPlans" : 1,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {
                    "a.b" : [ 
                        [ 
                            2035284135, 
                            2035284135
                        ]
                    ],
                    "x" : [ 
                        [ 
                            {
                                "$maxElement" : 1
                            }, 
                            {
                                "$minElement" : 1
                            }
                        ]
                    ],
                    "_id" : [ 
                        [ 
                            {
                                "$minElement" : 1
                            }, 
                            {
                                "$maxElement" : 1
                            }
                        ]
                    ]
                },
                "allPlans" : [ 
                    {
                        "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                        "isMultiKey" : true,
                        "n" : 1,
                        "nscannedObjects" : 1,
                        "nscanned" : 1,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                            "a.b" : [ 
                                [ 
                                    2035284135, 
                                    2035284135
                                ]
                            ],
                            "x" : [ 
                                [ 
                                    {
                                        "$maxElement" : 1
                                    }, 
                                    {
                                        "$minElement" : 1
                                    }
                                ]
                            ],
                            "_id" : [ 
                                [ 
                                    {
                                        "$minElement" : 1
                                    }, 
                                    {
                                        "$maxElement" : 1
                                    }
                                ]
                            ]
                        }
                    }
                ],
                "server" : "xxx",
                "filterSet" : false,
                "stats" : {
                    "type" : "PROJECTION",
                    "works" : 2,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 1,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "isEOF" : 1,
                    "children" : [ 
                        {
                            "type" : "KEEP_MUTATIONS",
                            "works" : 2,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 1,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "children" : [ 
                                {
                                    "type" : "SHARDING_FILTER",
                                    "works" : 2,
                                    "yields" : 0,
                                    "unyields" : 0,
                                    "invalidates" : 0,
                                    "advanced" : 1,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "isEOF" : 1,
                                    "chunkSkips" : 0,
                                    "children" : [ 
                                        {
                                            "type" : "FETCH",
                                            "works" : 2,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 1,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "alreadyHasObj" : 0,
                                            "forcedFetches" : 0,
                                            "matchTested" : 0,
                                            "children" : [ 
                                                {
                                                    "type" : "IXSCAN",
                                                    "works" : 2,
                                                    "yields" : 0,
                                                    "unyields" : 0,
                                                    "invalidates" : 0,
                                                    "advanced" : 1,
                                                    "needTime" : 0,
                                                    "needFetch" : 0,
                                                    "isEOF" : 1,
                                                    "keyPattern" : "{ a.b: 1.0, x: -1.0, _id: 1.0 }",
                                                    "isMultiKey" : 1,
                                                    "boundsVerbose" : "field #0['a.b']: [2035284135.0, 2035284135.0], field #1['x']: [MaxKey, MinKey], field #2['_id']: [MinKey, MaxKey]",
                                                    "yieldMovedCursor" : 0,
                                                    "dupsTested" : 1,
                                                    "dupsDropped" : 0,
                                                    "seenInvalidated" : 0,
                                                    "matchTested" : 0,
                                                    "keysExamined" : 1,
                                                    "children" : []
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "repset3" : [ 
            {
                "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                "isMultiKey" : true,
                "n" : 0,
                "nscannedObjects" : 0,
                "nscanned" : 0,
                "nscannedObjectsAllPlans" : 0,
                "nscannedAllPlans" : 0,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {
                    "a.b" : [ 
                        [ 
                            2035284135, 
                            2035284135
                        ]
                    ],
                    "x" : [ 
                        [ 
                            {
                                "$maxElement" : 1
                            }, 
                            {
                                "$minElement" : 1
                            }
                        ]
                    ],
                    "_id" : [ 
                        [ 
                            {
                                "$minElement" : 1
                            }, 
                            {
                                "$maxElement" : 1
                            }
                        ]
                    ]
                },
                "allPlans" : [ 
                    {
                        "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                        "isMultiKey" : true,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                            "a.b" : [ 
                                [ 
                                    2035284135, 
                                    2035284135
                                ]
                            ],
                            "x" : [ 
                                [ 
                                    {
                                        "$maxElement" : 1
                                    }, 
                                    {
                                        "$minElement" : 1
                                    }
                                ]
                            ],
                            "_id" : [ 
                                [ 
                                    {
                                        "$minElement" : 1
                                    }, 
                                    {
                                        "$maxElement" : 1
                                    }
                                ]
                            ]
                        }
                    }
                ],
                "server" : "xxx",
                "filterSet" : false,
                "stats" : {
                    "type" : "PROJECTION",
                    "works" : 1,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 0,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "isEOF" : 1,
                    "children" : [ 
                        {
                            "type" : "KEEP_MUTATIONS",
                            "works" : 1,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 0,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "children" : [ 
                                {
                                    "type" : "SHARDING_FILTER",
                                    "works" : 1,
                                    "yields" : 0,
                                    "unyields" : 0,
                                    "invalidates" : 0,
                                    "advanced" : 0,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "isEOF" : 1,
                                    "chunkSkips" : 0,
                                    "children" : [ 
                                        {
                                            "type" : "FETCH",
                                            "works" : 1,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 0,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "alreadyHasObj" : 0,
                                            "forcedFetches" : 0,
                                            "matchTested" : 0,
                                            "children" : [ 
                                                {
                                                    "type" : "IXSCAN",
                                                    "works" : 1,
                                                    "yields" : 0,
                                                    "unyields" : 0,
                                                    "invalidates" : 0,
                                                    "advanced" : 0,
                                                    "needTime" : 0,
                                                    "needFetch" : 0,
                                                    "isEOF" : 1,
                                                    "keyPattern" : "{ a.b: 1.0, x: -1.0, _id: 1.0 }",
                                                    "isMultiKey" : 1,
                                                    "boundsVerbose" : "field #0['a.b']: [2035284135.0, 2035284135.0], field #1['x']: [MaxKey, MinKey], field #2['_id']: [MinKey, MaxKey]",
                                                    "yieldMovedCursor" : 0,
                                                    "dupsTested" : 0,
                                                    "dupsDropped" : 0,
                                                    "seenInvalidated" : 0,
                                                    "matchTested" : 0,
                                                    "keysExamined" : 0,
                                                    "children" : []
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "repset4" : [ 
            {
                "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                "isMultiKey" : true,
                "n" : 0,
                "nscannedObjects" : 0,
                "nscanned" : 0,
                "nscannedObjectsAllPlans" : 0,
                "nscannedAllPlans" : 0,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {
                    "a.b" : [ 
                        [ 
                            2035284135, 
                            2035284135
                        ]
                    ],
                    "x" : [ 
                        [ 
                            {
                                "$maxElement" : 1
                            }, 
                            {
                                "$minElement" : 1
                            }
                        ]
                    ],
                    "_id" : [ 
                        [ 
                            {
                                "$minElement" : 1
                            }, 
                            {
                                "$maxElement" : 1
                            }
                        ]
                    ]
                },
                "allPlans" : [ 
                    {
                        "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                        "isMultiKey" : true,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                            "a.b" : [ 
                                [ 
                                    2035284135, 
                                    2035284135
                                ]
                            ],
                            "x" : [ 
                                [ 
                                    {
                                        "$maxElement" : 1
                                    }, 
                                    {
                                        "$minElement" : 1
                                    }
                                ]
                            ],
                            "_id" : [ 
                                [ 
                                    {
                                        "$minElement" : 1
                                    }, 
                                    {
                                        "$maxElement" : 1
                                    }
                                ]
                            ]
                        }
                    }
                ],
                "server" : "xxx",
                "filterSet" : false,
                "stats" : {
                    "type" : "PROJECTION",
                    "works" : 1,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 0,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "isEOF" : 1,
                    "children" : [ 
                        {
                            "type" : "KEEP_MUTATIONS",
                            "works" : 1,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 0,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "children" : [ 
                                {
                                    "type" : "SHARDING_FILTER",
                                    "works" : 1,
                                    "yields" : 0,
                                    "unyields" : 0,
                                    "invalidates" : 0,
                                    "advanced" : 0,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "isEOF" : 1,
                                    "chunkSkips" : 0,
                                    "children" : [ 
                                        {
                                            "type" : "FETCH",
                                            "works" : 1,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 0,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "alreadyHasObj" : 0,
                                            "forcedFetches" : 0,
                                            "matchTested" : 0,
                                            "children" : [ 
                                                {
                                                    "type" : "IXSCAN",
                                                    "works" : 1,
                                                    "yields" : 0,
                                                    "unyields" : 0,
                                                    "invalidates" : 0,
                                                    "advanced" : 0,
                                                    "needTime" : 0,
                                                    "needFetch" : 0,
                                                    "isEOF" : 1,
                                                    "keyPattern" : "{ a.b: 1.0, x: -1.0, _id: 1.0 }",
                                                    "isMultiKey" : 1,
                                                    "boundsVerbose" : "field #0['a.b']: [2035284135.0, 2035284135.0], field #1['x']: [MaxKey, MinKey], field #2['_id']: [MinKey, MaxKey]",
                                                    "yieldMovedCursor" : 0,
                                                    "dupsTested" : 0,
                                                    "dupsDropped" : 0,
                                                    "seenInvalidated" : 0,
                                                    "matchTested" : 0,
                                                    "keysExamined" : 0,
                                                    "children" : []
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        ],
        "repset5" : [ 
            {
                "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                "isMultiKey" : true,
                "n" : 0,
                "nscannedObjects" : 0,
                "nscanned" : 0,
                "nscannedObjectsAllPlans" : 0,
                "nscannedAllPlans" : 0,
                "scanAndOrder" : false,
                "indexOnly" : false,
                "nYields" : 0,
                "nChunkSkips" : 0,
                "millis" : 0,
                "indexBounds" : {
                    "a.b" : [ 
                        [ 
                            2035284135, 
                            2035284135
                        ]
                    ],
                    "x" : [ 
                        [ 
                            {
                                "$maxElement" : 1
                            }, 
                            {
                                "$minElement" : 1
                            }
                        ]
                    ],
                    "_id" : [ 
                        [ 
                            {
                                "$minElement" : 1
                            }, 
                            {
                                "$maxElement" : 1
                            }
                        ]
                    ]
                },
                "allPlans" : [ 
                    {
                        "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
                        "isMultiKey" : true,
                        "n" : 0,
                        "nscannedObjects" : 0,
                        "nscanned" : 0,
                        "scanAndOrder" : false,
                        "indexOnly" : false,
                        "nChunkSkips" : 0,
                        "indexBounds" : {
                            "a.b" : [ 
                                [ 
                                    2035284135, 
                                    2035284135
                                ]
                            ],
                            "x" : [ 
                                [ 
                                    {
                                        "$maxElement" : 1
                                    }, 
                                    {
                                        "$minElement" : 1
                                    }
                                ]
                            ],
                            "_id" : [ 
                                [ 
                                    {
                                        "$minElement" : 1
                                    }, 
                                    {
                                        "$maxElement" : 1
                                    }
                                ]
                            ]
                        }
                    }
                ],
                "server" : "xxx",
                "filterSet" : false,
                "stats" : {
                    "type" : "PROJECTION",
                    "works" : 1,
                    "yields" : 0,
                    "unyields" : 0,
                    "invalidates" : 0,
                    "advanced" : 0,
                    "needTime" : 0,
                    "needFetch" : 0,
                    "isEOF" : 1,
                    "children" : [ 
                        {
                            "type" : "KEEP_MUTATIONS",
                            "works" : 1,
                            "yields" : 0,
                            "unyields" : 0,
                            "invalidates" : 0,
                            "advanced" : 0,
                            "needTime" : 0,
                            "needFetch" : 0,
                            "isEOF" : 1,
                            "children" : [ 
                                {
                                    "type" : "SHARDING_FILTER",
                                    "works" : 1,
                                    "yields" : 0,
                                    "unyields" : 0,
                                    "invalidates" : 0,
                                    "advanced" : 0,
                                    "needTime" : 0,
                                    "needFetch" : 0,
                                    "isEOF" : 1,
                                    "chunkSkips" : 0,
                                    "children" : [ 
                                        {
                                            "type" : "FETCH",
                                            "works" : 1,
                                            "yields" : 0,
                                            "unyields" : 0,
                                            "invalidates" : 0,
                                            "advanced" : 0,
                                            "needTime" : 0,
                                            "needFetch" : 0,
                                            "isEOF" : 1,
                                            "alreadyHasObj" : 0,
                                            "forcedFetches" : 0,
                                            "matchTested" : 0,
                                            "children" : [ 
                                                {
                                                    "type" : "IXSCAN",
                                                    "works" : 1,
                                                    "yields" : 0,
                                                    "unyields" : 0,
                                                    "invalidates" : 0,
                                                    "advanced" : 0,
                                                    "needTime" : 0,
                                                    "needFetch" : 0,
                                                    "isEOF" : 1,
                                                    "keyPattern" : "{ a.b: 1.0, x: -1.0, _id: 1.0 }",
                                                    "isMultiKey" : 1,
                                                    "boundsVerbose" : "field #0['a.b']: [2035284135.0, 2035284135.0], field #1['x']: [MaxKey, MinKey], field #2['_id']: [MinKey, MaxKey]",
                                                    "yieldMovedCursor" : 0,
                                                    "dupsTested" : 0,
                                                    "dupsDropped" : 0,
                                                    "seenInvalidated" : 0,
                                                    "matchTested" : 0,
                                                    "keysExamined" : 0,
                                                    "children" : []
                                                }
                                            ]
                                        }
                                    ]
                                }
                            ]
                        }
                    ]
                }
            }
        ]
    },
    "cursor" : "BtreeCursor a.b_1_x_-1__id_1",
    "n" : 1,
    "nChunkSkips" : 0,
    "nYields" : 0,
    "nscanned" : 1,
    "nscannedAllPlans" : 1,
    "nscannedObjects" : 1,
    "nscannedObjectsAllPlans" : 1,
    "millisShardTotal" : 0,
    "millisShardAvg" : 0,
    "numQueries" : 5,
    "numShards" : 5,
    "millis" : 8
}

Comment by Daniel Breitlauch [ 11/Dec/14 ]

Both are run through mongos.
I will post an explain tomorrow.
The indices are there on every shard. Actually the indices are not new.
They are used and serving their purpose for a long time.

Comment by David Storch [ 11/Dec/14 ]

Hi breitlauch,

Thanks for the prompt response. Here are some more questions:

  1. Can you confirm that the problematic update and the matching queries are being run through mongos?
  2. Could you post the output of running the query with .explain() through mongos?
  3. Did you run getIndexes() via mongos? If so, could you paste the output from running getIndexes() on the collection after connecting directly to each shard?

Best,
Dave

Comment by Daniel Breitlauch [ 11/Dec/14 ]

Yes we enabled the --notablescan option for this.
The rest of the update:

{
    $set : {"lala" : "ma"}
},
{
        upsert : false,
        multi : true
    }

The indices:

/* 0 */
{
    "0" : {
        "v" : 1,
        "key" : {
            "_id" : 1
        },
        "name" : "_id_",
 
    },
    "1" : {
        "v" : 1,
        "key" : {
            "_id" : "hashed"
        },
        "name" : "_id_hashed",
 
    },
    "2" : {
        "v" : 1,
        "key" : {
            "a.b" : 1,
            "x" : -1,
            "_id" : 1
        },
        "name" : "a.b_1_x_-1__id_1",
 
        "background" : true
    },
    "3" : {
        "v" : 1,
        "key" : {
            "a.y.z2" : 1
        },
        "name" : "a.y.z2_1",
 
        "background" : true,
        "sparse" : true
    },
    "4" : {
        "v" : 1,
        "key" : {
            "a.y.z" : 1
        },
        "name" : "a.y.z_1",
 
        "background" : true,
        "sparse" : true
    },
}

BTW… the collection is sharded by _id_hashed

Comment by David Storch [ 11/Dec/14 ]

Hi breitlauch,

A few questions which can help me track this down:

  1. To confirm, you're running with the --notablescan option, correct?
  2. Would you be able to paste a log line from one of the queries that succeeds as you describe?
  3. Could you paste the output of getIndexes() on each of the collections for which you are experiencing this problem?
  4. From the provided information, it looks like the query part of the update which is failing is {"a.b": 1234}. Can you paste the document specifying the update modifiers as well? This is what you pass as the second argument to the update shell helper.

#3 and $4 in particular will help me to diagnose.

Thanks,
Dave

Comment by Daniel Breitlauch [ 11/Dec/14 ]

The exception:

com.mongodb.WriteConcernException: { "serverUsed" : "localhost:27017" , "ok" : 1 , "n" : 0 , "updatedExisting" : false , "err" : "could not get runner { a.b: 1234 };  :: caused by :: error processing query: ns=db.collection limit=0 skip=0
Tree: a.b == 1234
Sort: {}
Proj: {}
No query solutions" , "code" : 17243}
	at com.mongodb.CommandResult.getWriteException(CommandResult.java:90) ~[mongo-java-driver-2.12.3.jar:na]
	at com.mongodb.CommandResult.getException(CommandResult.java:79) ~[mongo-java-driver-2.12.3.jar:na]
	at com.mongodb.DBCollectionImpl.translateBulkWriteException(DBCollectionImpl.java:314) ~[mongo-java-driver-2.12.3.jar:na]
	at com.mongodb.DBCollectionImpl.update(DBCollectionImpl.java:272) ~[mongo-java-driver-2.12.3.jar:na]
	at com.mongodb.DBCollection.update(DBCollection.java:191) ~[mongo-java-driver-2.12.3.jar:na]

Comment by Daniel Breitlauch [ 11/Dec/14 ]

Sorry to reopen this problem but it reappeared.
We are now using 2.6.5 and the strange thing is that queries use the index but updates with the same query part do not.
Even stranger… I tested another db/collection with the same kind of nested array query/update and there both query and update work.
There is virtually no difference between these collections.
I will post the explains of both queries although an explain of the update would be more interesting.

I suggest to check the differences in the query planner for updates and queries.
There seams to be a difference producing the bug.

Comment by David Storch [ 09/Dec/14 ]

Thanks for getting back to us Daniel. Glad to hear that the problem has been resolved.

Comment by Daniel Breitlauch [ 09/Dec/14 ]

Sorry for the long delay. We updated to 2.6.5. and the problem is gone now.

Thanks for all the help,
Daniel

Comment by David Storch [ 05/Dec/14 ]

Hi breitlauch,

Please let us know if there is any more information you can provide to help us reproduce or diagnose a potential bug in the query system. If I don't hear back from you in a week, I plan to close this ticket as Incomplete. Hope we can help!

Best,
Dave

Comment by David Storch [ 01/Dec/14 ]

Hi breitlauch,

Thanks for the bug report! When I follow the steps to reproduce provided above, I do not find any evidence of a bug with --notablescan or with the query planner. Please refer to the shell session below, run against a standalone version 2.6.5 server:

> db.version()
2.6.5
 
// Create a collection with a single document.
> db.foo.drop()
true
> db.foo.insert({
...     "_id" : 1,
...     "a" : [
...             { "b" : 1 }
...     ],
...     "c" : [ 1, 2, 3],
...     "l" : 1
... })
WriteResult({ "nInserted" : 1 })
> db.foo.find()
{ "_id" : 1, "a" : [ { "b" : 1 } ], "c" : [ 1, 2, 3 ], "l" : 1 }
 
// Set --notablescan. This will cause queries or updates to fail if their plan is a collection scan.
> db.adminCommand({setParameter: 1, notablescan: true})
{ "was" : false, "ok" : 1 }
 
// This update fails, as its only available plan is a collection scan, and --notablescan is set.
> db.foo.update({ "a.b" : 1, "c" : 1 }, { $pull: { "c" : 1 }, $set: { "l" : 1 } })
WriteResult({
	"nMatched" : 0,
	"nUpserted" : 0,
	"nModified" : 0,
	"writeError" : {
		"code" : 17243,
		"errmsg" : "could not get runner { a.b: 1.0, c: 1.0 };  :: caused by :: error processing query: ns=test.foo limit=0 skip=0\nTree: $and\n    a.b == 1.0\n    c == 1.0\nSort: {}\nProj: {}\n No query solutions"
	}
})
 
// After adding an index, the same update succeeds.
> db.foo.ensureIndex({
...     "a.b" : 1,
...     "x" : -1,
...     "_id" : 1
... })
{
	"createdCollectionAutomatically" : false,
	"numIndexesBefore" : 1,
	"numIndexesAfter" : 2,
	"ok" : 1
}
> db.foo.ensureIndex({     "a.b" : 1,     "x" : -1,     "_id" : 1 })
{ "numIndexesBefore" : 2, "note" : "all indexes already exist", "ok" : 1 }

Please let us know if you continue to experience an issue with 2.6.5, or if you can provide more detail that could help us to reproduce/diagnose a bug. In the meantime, I am going to move this ticket to a "Waiting for User" state. I'll also try to address some of your specific questions below.

Are there other mechanism to prevent table scans?

The --notablescan option is the preferred method for strictly enforcing that the planner will never execute a collection scan. A less drastic option which you might consider is setting $maxScan on your queries in order to prevent disastrous slow queries from scanning too much data.

Sadly the planner always includes the table scan solution even when it has other (faster) options and --notablescan is activated.

The planner never generates both indexed solutions and a collection scan. If a query has one or more indexed plans, then an index will always be used to resolve the query. If, however, no indexes are available to answer the query, then the planner falls back on a collection scan. When it falls back to a collection scan, it checks --notablescan. If --notablescan is set, then the planner throws an error rather than proceeding with the collection scan.

There are multiple mitigating solutions like implement hints for updates or having the option to exclude table scans in index filters but this seams really like a bug in the query planner.

Hints or index filters are best used to ensure that the query planner is picking the correct index among several candidate indices. They are not intended as an alternative for --notablescan, i.e. they are not used for preventing collection scans. Perhaps the behavior you are seeing is due to a slow indexed solution rather than a collection scan?

Best,
Dave

Comment by Daniel Breitlauch [ 28/Nov/14 ]

We will definitely update to 2.6.5.
The .explain(true) shows that it is using the index and manual request get through.
I will report how it behaves after the update.
Makes no sense to chaise the bug with lots of changes already done.

Comment by Asya Kamsky [ 27/Nov/14 ]

breitlauch have you considered upgrading to 2.6.5 - 2.6.1 had a large number of query optimizer bugs that have been fixed since then.

Comment by Asya Kamsky [ 27/Nov/14 ]

Could you provide some additional information, like an explain(true) output for the query (find with the same condition the update is passed)?

Generated at Thu Feb 08 03:40:43 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.