Should I use hint or index filters in production?

Working with Data
1

I have a query that runs much faster when using hint than without. It takes ~300 ms to run without the hint and ~10 ms with the hint. I think this is due to the query planner as the explain(“executionStats”) show that the query planner looks at around 15 indexes before executing the query.

I am wondering if it makes more sense to use a hint or an index filter in production code

I am thinking hint would be better because it is very transparent in code, however it breaks when the index is not present.

Is there any way to make the hint not break when the index is not present? Without doing an additional round trip to the database to check for the index?

Or is there a way to make the index filter more transparent in the code base?

2

If it runs slower without the hint all the time then it’s not because of planning, it’s because it’s using the “wrong” or suboptimal index instead of the one you’re hinting. The right solution would be to figure out why - that would suggest hinting or using index filter or maybe something else.

Can you show us both the explain output and also a log line of this query when it runs without the hint (it will show which index it’s using and how long it’s taking).

Asya

2 Likes
3

Thanks for the reply!
So I wasn’t clear in my original question. The query planner picks the optima index, but even though it uses the correct index it still takes much longer.
Below are two explain(“executionStats”) results for the same query. I ran the one with the hint first (in order to avoid caching affecting the results).
The hinted query used the index: location_1_status_1_partialText_1__kind_1 and took 2 ms
The unhinted query used the index: location_1_status_1_partialText_1__kind_1 and took 246ms
Looking at the executionStages the executionTimeMillisEstimate is 0-1ms for all 3 stages in both queries.
So I can’t really account for the massive difference.

4 
{
"queryPlanner": {
  "plannerVersion": 1,
  "namespace": "redacted.people",
  "indexFilterSet": false,
  "parsedQuery": {
    "$and": [
      {
        "accessGroups": {
          "$elemMatch": {
            "$eq": ObjectId("5ad8b1d89bebc07d33817f57")
          }
        }
      },
      {
        "_kind": {
          "$eq": "Worker"
        }
      },
      {
        "location": {
          "$eq": ObjectId("5ad8b1d89bebc07d33817f57")
        }
      },
      {
        "status": {
          "$eq": "active"
        }
      },
      {
        "partialText": {
          "$regex": "^ni"
        }
      }
    ]
  },
  "winningPlan": {
    "stage": "SORT",
    "sortPattern": {
      "first": 1
    },
    "memLimit": 104857600,
    "limitAmount": 31,
    "type": "simple",
    "inputStage": {
      "stage": "FETCH",
      "filter": {
        "accessGroups": {
          "$elemMatch": {
            "$eq": ObjectId("5ad8b1d89bebc07d33817f57")
          }
        }
      },
      "inputStage": {
        "stage": "IXSCAN",
        "keyPattern": {
          "location": 1,
          "status": 1,
          "partialText": 1,
          "_kind": 1
        },
        "indexName": "location_1_status_1_partialText_1__kind_1",
        "isMultiKey": true,
        "multiKeyPaths": {
          "location": [],
          "status": [],
          "partialText": [
            "partialText"
          ],
          "_kind": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": true,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "location": [
            "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
          ],
          "status": [
            "[\"active\", \"active\"]"
          ],
          "partialText": [
            "[\"ni\", \"nj\")",
            "[/^ni/, /^ni/]"
          ],
          "_kind": [
            "[\"Worker\", \"Worker\"]"
          ]
        }
      }
    }
  },
  "rejectedPlans": []
},
"executionStats": {
  "executionSuccess": true,
  "nReturned": 31,
  "executionTimeMillis": 2,
  "totalKeysExamined": 1654,
  "totalDocsExamined": 115,
  "executionStages": {
    "stage": "SORT",
    "nReturned": 31,
    "executionTimeMillisEstimate": 0,
    "works": 1686,
    "advanced": 31,
    "needTime": 1654,
    "needYield": 0,
    "saveState": 1,
    "restoreState": 1,
    "isEOF": 1,
    "sortPattern": {
      "first": 1
    },
    "memLimit": 104857600,
    "limitAmount": 31,
    "type": "simple",
    "totalDataSizeSorted": 303466,
    "usedDisk": false,
    "inputStage": {
      "stage": "FETCH",
      "filter": {
        "accessGroups": {
          "$elemMatch": {
            "$eq": ObjectId("5ad8b1d89bebc07d33817f57")
          }
        }
      },
      "nReturned": 115,
      "executionTimeMillisEstimate": 0,
      "works": 1654,
      "advanced": 115,
      "needTime": 1538,
      "needYield": 0,
      "saveState": 1,
      "restoreState": 1,
      "isEOF": 1,
      "docsExamined": 115,
      "alreadyHasObj": 0,
      "inputStage": {
        "stage": "IXSCAN",
        "nReturned": 115,
        "executionTimeMillisEstimate": 0,
        "works": 1654,
        "advanced": 115,
        "needTime": 1538,
        "needYield": 0,
        "saveState": 1,
        "restoreState": 1,
        "isEOF": 1,
        "keyPattern": {
          "location": 1,
          "status": 1,
          "partialText": 1,
          "_kind": 1
        },
        "indexName": "location_1_status_1_partialText_1__kind_1",
        "isMultiKey": true,
        "multiKeyPaths": {
          "location": [],
          "status": [],
          "partialText": [
            "partialText"
          ],
          "_kind": []
        },
        "isUnique": false,
        "isSparse": false,
        "isPartial": true,
        "indexVersion": 2,
        "direction": "forward",
        "indexBounds": {
          "location": [
            "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
          ],
          "status": [
            "[\"active\", \"active\"]"
          ],
          "partialText": [
            "[\"ni\", \"nj\")",
            "[/^ni/, /^ni/]"
          ],
          "_kind": [
            "[\"Worker\", \"Worker\"]"
          ]
        },
        "keysExamined": 1654,
        "seeks": 2,
        "dupsTested": 1652,
        "dupsDropped": 1537
      }
    }
  }
},
"serverInfo": {
  "host": "prod-shard-00-00-oioie.mongodb.net",
  "port": 27017,
  "version": "4.4.8",
  "gitVersion": "83b8bb8b6b325d8d8d3dfd2ad9f744bdad7d6ca0"
},
"ok": 1.0,
"$clusterTime": {
  "clusterTime": Timestamp(1630670604,
  4),
  "signature": {
    "hash": {
      "$binary": "IbGMJKU9zjK0gKYbnHGq93zo8p0=",
      "$type": "00"
    },
    "keyId": NumberLong(6944366697725820929)
  }
},
"operationTime": Timestamp(1630670604,
4)
}
5 
{
    "queryPlanner" : {
        "plannerVersion" : 1,
        "namespace" : "redacted.people",
        "indexFilterSet" : false,
        "parsedQuery" : {
            "$and" : [ 
                {
                    "accessGroups" : {
                        "$elemMatch" : {
                            "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                        }
                    }
                }, 
                {
                    "_kind" : {
                        "$eq" : "Worker"
                    }
                }, 
                {
                    "location" : {
                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                    }
                }, 
                {
                    "status" : {
                        "$eq" : "active"
                    }
                }, 
                {
                    "partialText" : {
                        "$regex" : "^ni"
                    }
                }
            ]
        },
        "winningPlan" : {
            "stage" : "SORT",
            "sortPattern" : {
                "first" : 1
            },
            "memLimit" : 104857600,
            "limitAmount" : 31,
            "type" : "simple",
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "accessGroups" : {
                        "$elemMatch" : {
                            "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                        }
                    }
                },
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "location" : 1,
                        "status" : 1,
                        "partialText" : 1,
                        "_kind" : 1
                    },
                    "indexName" : "location_1_status_1_partialText_1__kind_1",
                    "isMultiKey" : true,
                    "multiKeyPaths" : {
                        "location" : [],
                        "status" : [],
                        "partialText" : [ 
                            "partialText"
                        ],
                        "_kind" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : true,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "location" : [ 
                            "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                        ],
                        "status" : [ 
                            "[\"active\", \"active\"]"
                        ],
                        "partialText" : [ 
                            "[\"ni\", \"nj\")", 
                            "[/^ni/, /^ni/]"
                        ],
                        "_kind" : [ 
                            "[\"Worker\", \"Worker\"]"
                        ]
                    }
                }
            }
        },
        "rejectedPlans" : [ 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "first" : 1
                },
                "memLimit" : 104857600,
                "limitAmount" : 31,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "_kind" : {
                                    "$eq" : "Worker"
                                }
                            }, 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "active"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "location" : 1.0,
                            "last" : 1.0
                        },
                        "indexName" : "location_1_last_1",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "location" : [],
                            "last" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 1,
                        "direction" : "forward",
                        "indexBounds" : {
                            "location" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ],
                            "last" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "first" : 1
                },
                "memLimit" : 104857600,
                "limitAmount" : 31,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "active"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "location" : 1,
                            "_kind" : 1,
                            "last" : 1,
                            "first" : 1
                        },
                        "indexName" : "location_kind_last_first",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "location" : [],
                            "_kind" : [],
                            "last" : [],
                            "first" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "location" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ],
                            "_kind" : [ 
                                "[\"Worker\", \"Worker\"]"
                            ],
                            "last" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "first" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "first" : 1
                },
                "memLimit" : 104857600,
                "limitAmount" : 31,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "location" : 1,
                            "_kind" : 1,
                            "email" : 1,
                            "status" : 1
                        },
                        "indexName" : "location_kind_email_status",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "location" : [],
                            "_kind" : [],
                            "email" : [],
                            "status" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "location" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ],
                            "_kind" : [ 
                                "[\"Worker\", \"Worker\"]"
                            ],
                            "email" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [ 
                                "[\"active\", \"active\"]"
                            ]
                        }
                    }
                }
            }, 
            {
                "stage" : "LIMIT",
                "limitAmount" : 31,
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "location" : 1,
                            "_kind" : 1,
                            "status" : 1,
                            "first" : 1,
                            "last" : 1
                        },
                        "indexName" : "location_kind_status_first_last",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "location" : [],
                            "_kind" : [],
                            "status" : [],
                            "first" : [],
                            "last" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "location" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ],
                            "_kind" : [ 
                                "[\"Worker\", \"Worker\"]"
                            ],
                            "status" : [ 
                                "[\"active\", \"active\"]"
                            ],
                            "first" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "last" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "first" : 1
                },
                "memLimit" : 104857600,
                "limitAmount" : 31,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "location" : 1.0,
                            "membership.membershipRenewalDay" : 1.0,
                            "_kind" : 1.0,
                            "membership.product.membershipFrequency" : 1.0,
                            "status" : 1.0,
                            "membership.paidUntil" : 1.0
                        },
                        "indexName" : "AutoInvoice",
                        "isMultiKey" : false,
                        "multiKeyPaths" : {
                            "location" : [],
                            "membership.membershipRenewalDay" : [],
                            "_kind" : [],
                            "membership.product.membershipFrequency" : [],
                            "status" : [],
                            "membership.paidUntil" : []
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "location" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ],
                            "membership.membershipRenewalDay" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "_kind" : [ 
                                "[\"Worker\", \"Worker\"]"
                            ],
                            "membership.product.membershipFrequency" : [ 
                                "[MinKey, MaxKey]"
                            ],
                            "status" : [ 
                                "[\"active\", \"active\"]"
                            ],
                            "membership.paidUntil" : [ 
                                "[MinKey, MaxKey]"
                            ]
                        }
                    }
                }
            }, 
            {
                "stage" : "SORT",
                "sortPattern" : {
                    "first" : 1
                },
                "memLimit" : 104857600,
                "limitAmount" : 31,
                "type" : "simple",
                "inputStage" : {
                    "stage" : "FETCH",
                    "filter" : {
                        "$and" : [ 
                            {
                                "accessGroups" : {
                                    "$elemMatch" : {
                                        "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                    }
                                }
                            }, 
                            {
                                "_kind" : {
                                    "$eq" : "Worker"
                                }
                            }, 
                            {
                                "partialText" : {
                                    "$regex" : "^ni"
                                }
                            }, 
                            {
                                "location" : {
                                    "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                                }
                            }, 
                            {
                                "status" : {
                                    "$eq" : "active"
                                }
                            }
                        ]
                    },
                    "inputStage" : {
                        "stage" : "IXSCAN",
                        "keyPattern" : {
                            "accessGroups" : 1
                        },
                        "indexName" : "accessGroups_1",
                        "isMultiKey" : true,
                        "multiKeyPaths" : {
                            "accessGroups" : [ 
                                "accessGroups"
                            ]
                        },
                        "isUnique" : false,
                        "isSparse" : false,
                        "isPartial" : false,
                        "indexVersion" : 2,
                        "direction" : "forward",
                        "indexBounds" : {
                            "accessGroups" : [ 
                                "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                            ]
                        }
                    }
                }
            }
    },
    "executionStats" : {
        "executionSuccess" : true,
        "nReturned" : 31,
        "executionTimeMillis" : 246,
        "totalKeysExamined" : 1654,
        "totalDocsExamined" : 115,
        "executionStages" : {
            "stage" : "SORT",
            "nReturned" : 31,
            "executionTimeMillisEstimate" : 0,
            "works" : 1686,
            "advanced" : 31,
            "needTime" : 1654,
            "needYield" : 0,
            "saveState" : 27,
            "restoreState" : 27,
            "isEOF" : 1,
            "sortPattern" : {
                "first" : 1
            },
            "memLimit" : 104857600,
            "limitAmount" : 31,
            "type" : "simple",
            "totalDataSizeSorted" : 303466,
            "usedDisk" : false,
            "inputStage" : {
                "stage" : "FETCH",
                "filter" : {
                    "accessGroups" : {
                        "$elemMatch" : {
                            "$eq" : ObjectId("5ad8b1d89bebc07d33817f57")
                        }
                    }
                },
                "nReturned" : 115,
                "executionTimeMillisEstimate" : 0,
                "works" : 1654,
                "advanced" : 115,
                "needTime" : 1538,
                "needYield" : 0,
                "saveState" : 27,
                "restoreState" : 27,
                "isEOF" : 1,
                "docsExamined" : 115,
                "alreadyHasObj" : 0,
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "nReturned" : 115,
                    "executionTimeMillisEstimate" : 0,
                    "works" : 1654,
                    "advanced" : 115,
                    "needTime" : 1538,
                    "needYield" : 0,
                    "saveState" : 27,
                    "restoreState" : 27,
                    "isEOF" : 1,
                    "keyPattern" : {
                        "location" : 1,
                        "status" : 1,
                        "partialText" : 1,
                        "_kind" : 1
                    },
                    "indexName" : "location_1_status_1_partialText_1__kind_1",
                    "isMultiKey" : true,
                    "multiKeyPaths" : {
                        "location" : [],
                        "status" : [],
                        "partialText" : [ 
                            "partialText"
                        ],
                        "_kind" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : true,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "location" : [ 
                            "[ObjectId('5ad8b1d89bebc07d33817f57'), ObjectId('5ad8b1d89bebc07d33817f57')]"
                        ],
                        "status" : [ 
                            "[\"active\", \"active\"]"
                        ],
                        "partialText" : [ 
                            "[\"ni\", \"nj\")", 
                            "[/^ni/, /^ni/]"
                        ],
                        "_kind" : [ 
                            "[\"Worker\", \"Worker\"]"
                        ]
                    },
                    "keysExamined" : 1654,
                    "seeks" : 2,
                    "dupsTested" : 1652,
                    "dupsDropped" : 1537
                }
            }
        }
    },
    "serverInfo" : {
        "host" : "prod-shard-00-00-oioie.mongodb.net",
        "port" : 27017,
        "version" : "4.4.8",
        "gitVersion" : "83b8bb8b6b325d8d8d3dfd2ad9f744bdad7d6ca0"
    },
    "ok" : 1.0,
    "$clusterTime" : {
        "clusterTime" : Timestamp(1630671158, 31),
        "signature" : {
            "hash" : { "$binary" : "o70Z/UqtrDL5KjYxdvRg+TYOj64=", "$type" : "00" },
            "keyId" : NumberLong(6944366697725820929)
        }
    },
    "operationTime" : Timestamp(1630671158, 31)
}
6

Sorry I couldn’t upload these as an attachment because I am a new user, and the explain without the hint has some plans omitted because it exceed the text limit

7

explain takes longer because of extra planning. I’m curious about regular queries using this index that take longer than hinted queries that use this index. Do you see anything like that in the logs?

8

Yeah I began looking into this query because queries of the same shape were showing up in the slow query profiler in Atlas. The above query took around 300ms in atlas using the location_1_status_1_partialText_1__kind_1 index

9

I believe that slow queries in Atlas allow you to get more details (basically the full log line) so I’d be curious whether the queries that take longer have a "storage":{} section with times waiting. The same query can take longer if it has to wait for data to be fetched from disk (read into the cache).