[SERVER-32186] Query Planner selects wrong index Created: 06/Dec/17  Updated: 08/Feb/23  Resolved: 16/Dec/17

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

Type: Bug Priority: Major - P3
Reporter: Dimitris Halatsis Assignee: Backlog - Query Team (Inactive)
Resolution: Duplicate Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-7568 Aggregation framework favors non-bloc... Closed
Assigned Teams:
Query
Operating System: ALL
Participants:

 Description   

I have a collection that has documents of the following form:

// Some comments here
{
	"_id:": ObjectId(),
	"timestamp": ISODate(),
	"fieldA": Number,
	"fieldB": String,
	.... // irrelevant fields
}

and the following indexes:

  1. _id index { _id :1}
  2. timestamp_index: { timestamp: 1 }
  3. compound_index: { fieldA: 1, fieldB: 1 }

The collection stores around 2.5 million documents

When I issue the query :

db.collection.find(
{
  "timestamp": {
    "$gte": ISODate("2017-11-29T14:55:15.682Z"),
    "$lte": ISODate("2017-12-06T10:49:36.833Z")
  },
  "fieldA": 0,
  "fieldB": "0013A20041673C35"
}).sort({timestamp: -1}).toArray()

The result is about 3500 documents
The queryPlanner evaluates the usage of first using the compound_index and rejects it. It finally uses the timestamp_index as a first IXSCAN stage and the rest of the query in a FETCH stage... This operation results in 2.5 seconds execution time

However if I enforce the usage of the compound index in the IXSCAN stage with the

.hint({fieldA: 1, fieldB: 1})

the execution time becomes 60 ms!!!

So how does the query planner evaluate the performance of each plan and reject it?

The real problem is that this is used in an aggregation where I cannot use the *hint * functionality to enforce index usage, so this is a big stepdown in performance...



 Comments   
Comment by Asya Kamsky [ 06/Dec/17 ]

This is likely the same issue as SERVER-7568 - in aggregation we will prefer the index that supports the sort.

Note that 3.6 (out earlier this week) allows specifying "hint": https://docs.mongodb.com/manual/release-notes/3.6/#new-options

Comment by Dimitris Halatsis [ 06/Dec/17 ]

Also let me add the explain results in both occasions.
This is the default one:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "db.Measurement",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "fieldA" : {
                        "$eq" : 0.0
                    }
                }, 
                {
                    "fieldB" : {
                        "$eq" : "0013A20041673C35"
                    }
                }, 
                {
                    "timestamp" : {
                        "$lte" : ISODate("2017-12-06T10:49:36.833Z")
                    }
                }, 
                {
                    "timestamp" : {
                        "$gte" : ISODate("2017-11-29T14:55:15.682Z")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [ 
                    {
                        "fieldA" : {
                            "$eq" : 0.0
                        }
                    }, 
                    {
                        "fieldB" : {
                            "$eq" : "0013A20041673C35"
                        }
                    }
                ]
            },
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "timestamp" : 1
                },
                "indexName" : "timestamp_index",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "timestamp" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "backward",
                "indexBounds" : {
                    "timestamp" : [ 
                        "[new Date(1512557376833), new Date(1511967315682)]"
                    ]
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "timestamp" : -1.0
                },
                "inputStage" : {
                    "stage" : "SORT_KEY_GENERATOR",
                    "inputStage" : {
                        "stage" : "FETCH",
                        "filter" : {
                            "$and" : [ 
                                {
                                    "timestamp" : {
                                        "$lte" : ISODate("2017-12-06T10:49:36.833Z")
                                    }
                                }, 
                                {
                                    "timestamp" : {
                                        "$gte" : ISODate("2017-11-29T14:55:15.682Z")
                                    }
                                }
                            ]
                        },
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "fieldA" : 1,
                                "fieldB" : 1
                            },
                            "indexName" : "fieldA_fieldB_index",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "fieldA" : [],
                                "fieldB" : []
                            },
                            "isUnique" : false,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "fieldA" : [ 
                                    "[0.0, 0.0]"
                                ],
                                "fieldB" : [ 
                                    "[\"0013A20041673C35\", \"0013A20041673C35\"]"
                                ]
                            }
                        }
                    }
                }
            }
        ]
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 3235,
        "executionTimeMillis" : 2934,
        "totalKeysExamined" : 1234298,
        "totalDocsExamined" : 1234298,
        "executionStages" : {
            "stage" : "FETCH",
            "filter" : {
                "$and" : [ 
                    {
                        "fieldA" : {
                            "$eq" : 0.0
                        }
                    }, 
                    {
                        "fieldB" : {
                            "$eq" : "0013A20041673C35"
                        }
                    }
                ]
            },
            "nReturned" : 3235,
            "executionTimeMillisEstimate" : 2790,
            "works" : 1234299,
            "advanced" : 3235,
            "needTime" : 1231063,
            "needYield" : 0,
            "saveState" : 9674,
            "restoreState" : 9674,
            "isEOF" : 1,
            "invalidates" : 0,
            "docsExamined" : 1234298,
            "alreadyHasObj" : 0,
            "inputStage" : {
                "stage" : "IXSCAN",
                "nReturned" : 1234298,
                "executionTimeMillisEstimate" : 476,
                "works" : 1234299,
                "advanced" : 1234298,
                "needTime" : 0,
                "needYield" : 0,
                "saveState" : 9674,
                "restoreState" : 9674,
                "isEOF" : 1,
                "invalidates" : 0,
                "keyPattern" : {
                    "timestamp" : 1
                },
                "indexName" : "timestamp_index",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "timestamp" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "backward",
                "indexBounds" : {
                    "timestamp" : [ 
                        "[new Date(1512557376833), new Date(1511967315682)]"
                    ]
                },
                "keysExamined" : 1234298,
                "seeks" : 1,
                "dupsTested" : 0,
                "dupsDropped" : 0,
                "seenInvalidated" : 0
            }
        }
    },
    "serverInfo" : {
        "host" : "mitsos-XPS-13-9360",
        "port" : 27017,
        "version" : "3.4.10",
        "gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
    },
    "ok" : 1.0
}

This one is with the hint:

{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "db.Measurement",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "fieldA" : {
                        "$eq" : 0.0
                    }
                }, 
                {
                    "fieldB" : {
                        "$eq" : "0013A20041673C35"
                    }
                }, 
                {
                    "timestamp" : {
                        "$lte" : ISODate("2017-12-06T10:49:36.833Z")
                    }
                }, 
                {
                    "timestamp" : {
                        "$gte" : ISODate("2017-11-29T14:55:15.682Z")
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "timestamp" : -1.0
            },
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "timestamp" : {
                                    "$lte" : ISODate("2017-12-06T10:49:36.833Z")
                                }
                            }, 
                            {
                                "timestamp" : {
                                    "$gte" : ISODate("2017-11-29T14:55:15.682Z")
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "fieldA" : 1,
                            "fieldB" : 1
                        },
                        "indexName" : "fieldA_fieldB_index",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "fieldA" : [],
                            "fieldB" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "fieldA" : [ 
                                "[0.0, 0.0]"
                            ],
                            "fieldB" : [ 
                                "[\"0013A20041673C35\", \"0013A20041673C35\"]"
                            ]
                        }
                    }
                }
            }
        },
        "rejectedPlans" : []
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 3235,
        "executionTimeMillis" : 24,
        "totalKeysExamined" : 8445,
        "totalDocsExamined" : 8445,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 3235,
            "executionTimeMillisEstimate" : 30,
            "works" : 11683,
            "advanced" : 3235,
            "needTime" : 8447,
            "needYield" : 0,
            "saveState" : 91,
            "restoreState" : 91,
            "isEOF" : 1,
            "invalidates" : 0,
            "sortPattern" : {
                "timestamp" : -1.0
            },
            "memUsage" : 905800,
            "memLimit" : 33554432,
            "inputStage" : {
                "stage" : "SORT_KEY_GENERATOR",
                "nReturned" : 3235,
                "executionTimeMillisEstimate" : 20,
                "works" : 8447,
                "advanced" : 3235,
                "needTime" : 5211,
                "needYield" : 0,
                "saveState" : 91,
                "restoreState" : 91,
                "isEOF" : 1,
                "invalidates" : 0,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "timestamp" : {
                                    "$lte" : ISODate("2017-12-06T10:49:36.833Z")
                                }
                            }, 
                            {
                                "timestamp" : {
                                    "$gte" : ISODate("2017-11-29T14:55:15.682Z")
                                }
                            }
                        ]
                    },
                    "nReturned" : 3235,
                    "executionTimeMillisEstimate" : 20,
                    "works" : 8446,
                    "advanced" : 3235,
                    "needTime" : 5210,
                    "needYield" : 0,
                    "saveState" : 91,
                    "restoreState" : 91,
                    "isEOF" : 1,
                    "invalidates" : 0,
                    "docsExamined" : 8445,
                    "alreadyHasObj" : 0,
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "nReturned" : 8445,
                        "executionTimeMillisEstimate" : 10,
                        "works" : 8446,
                        "advanced" : 8445,
                        "needTime" : 0,
                        "needYield" : 0,
                        "saveState" : 91,
                        "restoreState" : 91,
                        "isEOF" : 1,
                        "invalidates" : 0,
                        "keyPattern" : {
                            "fieldA" : 1,
                            "fieldB" : 1
                        },
                        "indexName" : "fieldA_fieldB_index",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "fieldA" : [],
                            "fieldB" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "fieldA" : [ 
                                "[0.0, 0.0]"
                            ],
                            "fieldB" : [ 
                                "[\"0013A20041673C35\", \"0013A20041673C35\"]"
                            ]
                        },
                        "keysExamined" : 8445,
                        "seeks" : 1,
                        "dupsTested" : 0,
                        "dupsDropped" : 0,
                        "seenInvalidated" : 0
                    }
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "mitsos-XPS-13-9360",
        "port" : 27017,
        "version" : "3.4.10",
        "gitVersion" : "078f28920cb24de0dd479b5ea6c66c644f6326e9"
    },
    "ok" : 1.0
}

Comment by Dimitris Halatsis [ 06/Dec/17 ]

Also, should't Index intersection be an option that would produce better results?

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