[SERVER-16404] Mongo 2.4 version: Can't select the correct Index to Use ! Created: 03/Dec/14  Updated: 23/Apr/15  Resolved: 23/Apr/15

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

Type: Bug Priority: Major - P3
Reporter: Safwen MARZOUGUI Assignee: Ramon Fernandez Marina
Resolution: Won't Fix Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Operating System: ALL
Participants:

 Description   

Hello,

Can't select the correct Index to Use !

Actually on forcing the use of a compounf Index that I have created : « PTY_OK_DS_DSL_PF_PT » with hint :

db.OperationalData.find(
{"PeriodType":"hour","OperatorKey":"DE-TSO-0004",    
        "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
    "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},    
    "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
).hint('PTY_OK_DS_DSL_PF_PT').explain(true)

Results:

    "cursor" : "BtreeCursor PTY_OK_DS_DSL_PF_PT multi",
    "isMultiKey" : false,
    "n" : 53879,
    "nscannedObjects" : 53879,
    "nscanned" : 62632,
    "nscannedObjectsAllPlans" : 53879,
    "nscannedAllPlans" : 62632,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 254,

If we leave the choise to Mongo to select the optimal index (we remove the « hint ») then :

db.OperationalData.find(
{"PeriodType":"hour","OperatorKey":"DE-TSO-0004",    
        "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"},
    "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"},    
    "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}
).explain(true)
 
    "cursor" : "BtreeCursor PeriodTo_1",
    "isMultiKey" : false,
    "n" : 53879,
    "nscannedObjects" : 2792389,
    "nscanned" : 2792389,
    "nscannedObjectsAllPlans" : 2801318,
    "nscannedAllPlans" : 2803528,
    "scanAndOrder" : false,
    "indexOnly" : false,
   "nYields" : 10,
    "nChunkSkips" : 0,
    "millis" : 11815,   !!!!!!

The difference is very Big !

Does this issue is fixed in later version?
It is clearly that Monogo have problems with calculating the correct index to use ...

This can produce very critical performance problem.....

Can you please check.
This is urgent.

Thanks to all the team.
BR,



 Comments   
Comment by Ramon Fernandez Marina [ 01/Apr/15 ]

Hi safwen, thanks for sending all the additional information, and my apologies for the late reply. MongoDB is choosing the PeriodTo_1 index because it believes it will be faster (see nscanned), but does indeed look like in this case it may not be the right choice for your query.

I'd encourage you to try a newer version of MongoDB (3.0 was released recently), and report back if the issue persists. As I said, it's highly unlikely that 2.4 will see any improvements at this stage. If you decide to stay in 2.4 you may want to continue to use hint(), but would upgrading be an option for you?

Regards,
Ramón.

Comment by Safwen MARZOUGUI [ 08/Dec/14 ]

Hello,

Any news about this issue?
Is it fixed in next verions?

Thanks, BR,

Comment by Safwen MARZOUGUI [ 04/Dec/14 ]

The first time I executed, it took nearly 20seconds.
The next other times, it tooks nearly the same time ~5sec (as sent).

First execution, I think that Mongo need an extra time to calculate the Query Plan cache,
Is that the case?

Comment by Safwen MARZOUGUI [ 04/Dec/14 ]

/* 0 */
{
    "cursor" : "BtreeCursor PeriodTo_1",
    "isMultiKey" : false,
    "n" : 53879,
    "nscannedObjects" : 2792389,
    "nscanned" : 2792389,
    "nscannedObjectsAllPlans" : 2801959,
    "nscannedAllPlans" : 2807997,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 6,
    "nChunkSkips" : 0,
    "millis" : 5210,
    "indexBounds" : {
        "PeriodTo" : [ 
            [ 
                "2014-10-01T00:00:00+02:00", 
                {}
            ]
        ]
    },
    "allPlans" : [ 
        {
            "cursor" : "BtreeCursor DataSetLabel_1 multi",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PeriodFrom_1",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor OperatorKey_1",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "OperatorKey" : [ 
                    [ 
                        "DE-TSO-0004", 
                        "DE-TSO-0004"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PeriodTo_1",
            "n" : 53853,
            "nscannedObjects" : 2792389,
            "nscanned" : 2792389,
            "indexBounds" : {
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PTY_OK_DSL_PF_PT multi",
            "n" : 26,
            "nscannedObjects" : 26,
            "nscanned" : 2236,
            "indexBounds" : {
                "PeriodType" : [ 
                    [ 
                        "hour", 
                        "hour"
                    ]
                ],
                "OperatorKey" : [ 
                    [ 
                        "DE-TSO-0004", 
                        "DE-TSO-0004"
                    ]
                ],
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ],
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ],
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PTY_OPD_DSL_PF_PT multi",
            "n" : 0,
            "nscannedObjects" : 6,
            "nscanned" : 2233,
            "indexBounds" : {
                "PeriodType" : [ 
                    [ 
                        "hour", 
                        "hour"
                    ]
                ],
                "OperatorPointDirection" : [ 
                    [ 
                        {
                            "$minElement" : 1
                        }, 
                        {
                            "$maxElement" : 1
                        }
                    ]
                ],
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ],
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ],
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PTY_DSL_PF_PT multi",
            "n" : 22,
            "nscannedObjects" : 635,
            "nscanned" : 2236,
            "indexBounds" : {
                "PeriodType" : [ 
                    [ 
                        "hour", 
                        "hour"
                    ]
                ],
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ],
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ],
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BasicCursor",
            "n" : 0,
            "nscannedObjects" : 2225,
            "nscanned" : 2225,
            "indexBounds" : {}
        }
    ],
    "oldPlan" : {
        "cursor" : "BtreeCursor PeriodTo_1",
        "indexBounds" : {
            "PeriodTo" : [ 
                [ 
                    "2014-10-01T00:00:00+02:00", 
                    {}
                ]
            ]
        }
    },
    "server" : "vmprjentsogoatmdb1:27017"
}

Comment by Ramon Fernandez Marina [ 04/Dec/14 ]

safwen, can you also post the output of one more command please?

db.OperationalData.find({"PeriodType":"hour","OperatorKey":"DE-TSO-0004", "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}, "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"}, "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"}}).explain(true)

Comment by Safwen MARZOUGUI [ 04/Dec/14 ]

Hello Ramon,

Here is what you asked:

db.OperationalData.getIndexes()
{
    "0" : {
        "v" : 1,
        "name" : "_id_",
        "key" : {
            "_id" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "1" : {
        "v" : 1,
        "name" : "DataSetLabel_1",
        "key" : {
            "DataSetLabel" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "2" : {
        "v" : 1,
        "name" : "Indicator_1",
        "key" : {
            "Indicator" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "3" : {
        "v" : 1,
        "name" : "OperatorPointDirection_1",
        "key" : {
            "OperatorPointDirection" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "4" : {
        "v" : 1,
        "name" : "PeriodFrom_1",
        "key" : {
            "PeriodFrom" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "5" : {
        "v" : 1,
        "name" : "OperatorKey_1",
        "key" : {
            "OperatorKey" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "6" : {
        "v" : 1,
        "name" : "DirectionKey_1",
        "key" : {
            "DirectionKey" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "7" : {
        "v" : 1,
        "name" : "PointKey_1",
        "key" : {
            "PointKey" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "8" : {
        "v" : 1,
        "name" : "PeriodTo_1",
        "key" : {
            "PeriodTo" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "9" : {
        "v" : 1,
        "name" : "LastUpdateDateTime_1",
        "key" : {
            "LastUpdateDateTime" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "10" : {
        "v" : 1,
        "name" : "InterruptionType_1",
        "key" : {
            "InterruptionType" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "11" : {
        "v" : 1,
        "name" : "CapacityType_1",
        "key" : {
            "CapacityType" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "12" : {
        "v" : 1,
        "name" : "CapacityCommercialType_1",
        "key" : {
            "CapacityCommercialType" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "13" : {
        "v" : 1,
        "name" : "CreationDateTime_1",
        "key" : {
            "CreationDateTime" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "14" : {
        "v" : 1,
        "name" : "I_PTY_OK_DSL_PF_PT",
        "key" : {
            "Indicator" : 1,
            "PeriodType" : 1,
            "OperatorKey" : 1,
            "DataSetLabel" : 1,
            "PeriodFrom" : 1,
            "PeriodTo" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "15" : {
        "v" : 1,
        "name" : "I_PTY_OPD_DSL_PF_PT",
        "key" : {
            "Indicator" : 1,
            "PeriodType" : 1,
            "OperatorPointDirection" : 1,
            "DataSetLabel" : 1,
            "PeriodFrom" : 1,
            "PeriodTo" : 1
        },
        "ns" : "tp.OperationalData"
    },
    "16" : {
        "v" : 1,
        "name" : "PTY_OK_DSL_PF_PT",
        "key" : {
            "PeriodType" : 1,
            "OperatorKey" : 1,
            "DataSetLabel" : 1,
            "PeriodFrom" : 1,
            "PeriodTo" : 1
        },
        "ns" : "tp.OperationalData"
    }
}

db.OperationalData.find({"PeriodType":"hour","OperatorKey":"DE-TSO-0004",  "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"}, "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"}, "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}).hint('PTY_OK_DSL_PF_PT').explain(true)
{
    "cursor" : "BtreeCursor PTY_OK_DSL_PF_PT multi",
    "isMultiKey" : false,
    "n" : 53879,
    "nscannedObjects" : 53879,
    "nscanned" : 62632,
    "nscannedObjectsAllPlans" : 53879,
    "nscannedAllPlans" : 62632,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 229,
    "indexBounds" : {
        "PeriodType" : [ 
            [ 
                "hour", 
                "hour"
            ]
        ],
        "OperatorKey" : [ 
            [ 
                "DE-TSO-0004", 
                "DE-TSO-0004"
            ]
        ],
        "DataSetLabel" : [ 
            [ 
                "CMP Auctions", 
                "CMP Auctions"
            ], 
            [ 
                "CMP Unavailable", 
                "CMP Unavailable"
            ], 
            [ 
                "CMP Unsuccessful requests", 
                "CMP Unsuccessful requests"
            ], 
            [ 
                "Generic Indicators", 
                "Generic Indicators"
            ], 
            [ 
                "Interruptions", 
                "Interruptions"
            ]
        ],
        "PeriodFrom" : [ 
            [ 
                "", 
                "2014-11-01T00:00:00+01:00"
            ]
        ],
        "PeriodTo" : [ 
            [ 
                "2014-10-01T00:00:00+02:00", 
                {}
            ]
        ]
    },
    "allPlans" : [ 
        {
            "cursor" : "BtreeCursor PTY_OK_DSL_PF_PT multi",
            "n" : 53879,
            "nscannedObjects" : 53879,
            "nscanned" : 62632,
            "indexBounds" : {
                "PeriodType" : [ 
                    [ 
                        "hour", 
                        "hour"
                    ]
                ],
                "OperatorKey" : [ 
                    [ 
                        "DE-TSO-0004", 
                        "DE-TSO-0004"
                    ]
                ],
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ],
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ],
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }
    ],
    "server" : "vmprjentsogoatmdb1:27017"
}

db.OperationalData.find({"PeriodType":"hour","OperatorKey":"DE-TSO-0004",  "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"}, "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"}, "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}).explain(true)
{
    "cursor" : "BtreeCursor PeriodTo_1",
    "isMultiKey" : false,
    "n" : 53879,
    "nscannedObjects" : 2792389,
    "nscanned" : 2792389,
    "nscannedObjectsAllPlans" : 2801318,
    "nscannedAllPlans" : 2803528,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 3,
    "nChunkSkips" : 0,
    "millis" : 5159,
    "indexBounds" : {
        "PeriodTo" : [ 
            [ 
                "2014-10-01T00:00:00+02:00", 
                {}
            ]
        ]
    },
    "allPlans" : [ 
        {
            "cursor" : "BtreeCursor DataSetLabel_1 multi",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PeriodFrom_1",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor OperatorKey_1",
            "n" : 0,
            "nscannedObjects" : 2226,
            "nscanned" : 2226,
            "indexBounds" : {
                "OperatorKey" : [ 
                    [ 
                        "DE-TSO-0004", 
                        "DE-TSO-0004"
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PeriodTo_1",
            "n" : 53853,
            "nscannedObjects" : 2792389,
            "nscanned" : 2792389,
            "indexBounds" : {
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BtreeCursor PTY_OK_DSL_PF_PT multi",
            "n" : 26,
            "nscannedObjects" : 26,
            "nscanned" : 2236,
            "indexBounds" : {
                "PeriodType" : [ 
                    [ 
                        "hour", 
                        "hour"
                    ]
                ],
                "OperatorKey" : [ 
                    [ 
                        "DE-TSO-0004", 
                        "DE-TSO-0004"
                    ]
                ],
                "DataSetLabel" : [ 
                    [ 
                        "CMP Auctions", 
                        "CMP Auctions"
                    ], 
                    [ 
                        "CMP Unavailable", 
                        "CMP Unavailable"
                    ], 
                    [ 
                        "CMP Unsuccessful requests", 
                        "CMP Unsuccessful requests"
                    ], 
                    [ 
                        "Generic Indicators", 
                        "Generic Indicators"
                    ], 
                    [ 
                        "Interruptions", 
                        "Interruptions"
                    ]
                ],
                "PeriodFrom" : [ 
                    [ 
                        "", 
                        "2014-11-01T00:00:00+01:00"
                    ]
                ],
                "PeriodTo" : [ 
                    [ 
                        "2014-10-01T00:00:00+02:00", 
                        {}
                    ]
                ]
            }
        }, 
        {
            "cursor" : "BasicCursor",
            "n" : 0,
            "nscannedObjects" : 2225,
            "nscanned" : 2225,
            "indexBounds" : {}
        }
    ],
    "oldPlan" : {
        "cursor" : "BtreeCursor PeriodTo_1",
        "indexBounds" : {
            "PeriodTo" : [ 
                [ 
                    "2014-10-01T00:00:00+02:00", 
                    {}
                ]
            ]
        }
    },
    "server" : "vmprjentsogoatmdb1:27017"
}

Thanks.
BR,

Comment by Ramon Fernandez Marina [ 03/Dec/14 ]

Can you please run the following commands and post the full output? The first one will list the indexes in the OperationalData collection, and the other two are the explain() output for the same queries above:

db.OperationalData.getIndexes()
db.OperationalData.find({"PeriodType":"hour","OperatorKey":"DE-TSO-0004",  "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"}, "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"}, "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}).hint('PTY_OK_DS_DSL_PF_PT').explain(true)
db.OperationalData.find({"PeriodType":"hour","OperatorKey":"DE-TSO-0004",  "PeriodFrom":{"$lte":"2014-11-01T00:00:00+01:00"}, "PeriodTo":{"$gte":"2014-10-01T00:00:00+02:00"}, "DataSetLabel":{"$in":["Generic Indicators","CMP Unsuccessful requests","CMP Auctions","CMP Unavailable","Interruptions"]}}).explain(true)

Note that if the query planner is choosing a sub-optimal index because of a bug it's very unlikely that such bug would be fixed in the 2.4 series, so I'd strongly encourage you to try the same queries using MongoDB 2.6.5 and let us know if the issue still persists.

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