Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-16341

Query planner chooses a table scan even an index exists

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Cannot Reproduce
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 2.6.1
    • Component/s: Index Maintenance, Querying
    • None
    • Fully Compatible
    • ALL
    • Hide

      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
}
      Show
      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 }

      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?

            Assignee:
            geert.bosch@mongodb.com Geert Bosch
            Reporter:
            breitlauch Daniel Breitlauch
            Votes:
            1 Vote for this issue
            Watchers:
            6 Start watching this issue

              Created:
              Updated:
              Resolved: