[SERVER-31064] query isn't index only unless projection references all indexed columns Created: 12/Sep/17  Updated: 09/Oct/17  Resolved: 14/Sep/17

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

Type: Bug Priority: Major - P3
Reporter: Mark Callaghan Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: File bug.js    
Issue Links:
Duplicate
duplicates SERVER-13197 Tighten index bounds and allow compou... Backlog
Operating System: ALL
Steps To Reproduce:

Run the script above. Output is:

WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
WriteResult({ "nInserted" : 1 })
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 2,
        "note" : "all indexes already exist",
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
 
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "price" : 1,
                                        "customerid" : 1
                                },
                                "indexName" : "price_1_customerid_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "price" : [ ],
                                        "customerid" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "price" : [
                                                "[MinKey, MaxKey]"
                                        ],
                                        "customerid" : [
                                                "[MinKey, MaxKey]"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "customerid" : {
                                "$lt" : 0
                        }
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "FETCH",
                                "filter" : {
                                        "customerid" : {
                                                "$lt" : 0
                                        }
                                },
                                "inputStage" : {
                                        "stage" : "IXSCAN",
                                        "keyPattern" : {
                                                "price" : 1,
                                                "customerid" : 1
                                        },
                                        "indexName" : "price_1_customerid_1",
                                        "isMultiKey" : false,
                                        "multiKeyPaths" : {
                                                "price" : [ ],
                                                "customerid" : [ ]
                                        },
                                        "isUnique" : false,
                                        "isSparse" : false,
                                        "isPartial" : false,
                                        "indexVersion" : 2,
                                        "direction" : "forward",
                                        "indexBounds" : {
                                                "price" : [
                                                        "[MinKey, MaxKey]"
                                                ],
                                                "customerid" : [
                                                        "[MinKey, MaxKey]"
                                                ]
                                        }
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
{
        "queryPlanner" : {
                "plannerVersion" : 1,
                "namespace" : "test.foo",
                "indexFilterSet" : false,
                "parsedQuery" : {
                        "$and" : [
                                {
                                        "customerid" : {
                                                "$lt" : 0
                                        }
                                },
                                {
                                        "price" : {
                                                "$gte" : 0
                                        }
                                }
                        ]
                },
                "winningPlan" : {
                        "stage" : "PROJECTION",
                        "transformBy" : {
                                "_id" : 0,
                                "price" : 1,
                                "customerid" : 1
                        },
                        "inputStage" : {
                                "stage" : "IXSCAN",
                                "keyPattern" : {
                                        "price" : 1,
                                        "customerid" : 1
                                },
                                "indexName" : "price_1_customerid_1",
                                "isMultiKey" : false,
                                "multiKeyPaths" : {
                                        "price" : [ ],
                                        "customerid" : [ ]
                                },
                                "isUnique" : false,
                                "isSparse" : false,
                                "isPartial" : false,
                                "indexVersion" : 2,
                                "direction" : "forward",
                                "indexBounds" : {
                                        "price" : [
                                                "[0.0, inf.0]"
                                        ],
                                        "customerid" : [
                                                "[-inf.0, 0.0)"
                                        ]
                                }
                        }
                },
                "rejectedPlans" : [ ]
        },
        "serverInfo" : {
                "host" : "nuc1",
                "port" : 27017,
                "version" : "3.4.6-1.7",
                "gitVersion" : "74b930309498503ee1c64d9a2526e45e2da464a2"
        },
        "ok" : 1
}
bye

Participants:

 Description   

In my test case below I expect the 3 queries at the end to be index only, but the second one isn't. The third query is like the second but it also references the first column in the index (price) and that query is index only.

db.foo.insert({ price:0, customerid:0});
db.foo.insert({ price:1, customerid:1});
db.foo.insert({ price:2, customerid:2});
 
db.foo.createIndex({ price:1, customerid:1});
 
db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
 
mdcallag@nuc1:~/b/pmon346$
mdcallag@nuc1:~/b/pmon346$
mdcallag@nuc1:~/b/pmon346$ cat bug.js
 
db.foo.insert({ price:0, customerid:0});
db.foo.insert({ price:1, customerid:1});
db.foo.insert({ price:2, customerid:2});
 
db.foo.createIndex({ price:1, customerid:1});
 
db.foo.find({}, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();
db.foo.find({ price : { $gte : 0 }, customerid : { $lt : 0 } }, { _id:0, price:1, customerid:1}).sort({price:1, customerid:1}).explain();



 Comments   
Comment by Kelsey Schubert [ 14/Sep/17 ]

Hi mdcallag,

Thank you for the detailed report. The issue you describe should be resolved by the completion of SERVER-13197. On the ticket, a workaround is presented using MinKey and MaxKey to add range filter on the first field of the index. Please feel free to vote for SERVER-13197 and watch it for updates.

Best regards,
Kelsey

Comment by Mark Callaghan [ 13/Sep/17 ]

When I have an index on (a,b,c)

  • with a predicate only on c I don't get index only
  • but with a predicate on a and c I do get index only

AFAIK, I need a predicate on the first column of the index, but not all of the columns, to get index only

Comment by Mark Callaghan [ 12/Sep/17 ]

I was using MongoDB 3.4.6

Comment by Mark Callaghan [ 12/Sep/17 ]

uploaded test case as pasting it isn't working

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