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

Index bounds over trailing field are not constrained for AND_HASH plan using a multikey index

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor - P4
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: Backlog
    • Component/s: Querying
    • Labels:
      None

      Description

      Trying to evaluate the behavior of index intersection (I enabled hash-based intersection manually), I noticed that for the following query with the following index the resulting plan doesn't have bounds for the "x" object:

      db.test.ensureIndex({mbox_id:1,x:1})
      db.test.insert({x: [1,2,3]}) // Makes the index multikey.
      db.adminCommand({setParameter: 1, internalQueryPlannerEnableHashIntersection: true}) // Enable AND_HASH.
      db.test.find({$and:[{mbox_id:1, x:{k:'name',v:'alex'}},{mbox_id:1, x:{k:'string',v:'dunno'}},{mbox_id:1, x:{k:'num',v:4}}]}).explain()
      

      			{
      				"stage" : "KEEP_MUTATIONS",
      				"inputStage" : {
      					"stage" : "FETCH",
      					"filter" : {
      						"$and" : [
      							{
      								"x" : {
      									"$eq" : {
      										"k" : "name",
      										"v" : "alex"
      									}
      								}
      							},
      							{
      								"x" : {
      									"$eq" : {
      										"k" : "string",
      										"v" : "dunno"
      									}
      								}
      							},
      							{
      								"x" : {
      									"$eq" : {
      										"k" : "num",
      										"v" : 4
      									}
      								}
      							}
      						]
      					},
      					"inputStage" : {
      						"stage" : "AND_HASH",
      						"inputStages" : [
      							{
      								"stage" : "IXSCAN",
      								"keyPattern" : {
      									"mbox_id" : 1,
      									"x" : 1
      								},
      								"indexName" : "mbox_id_1_x_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"mbox_id" : [
      										"[1.0, 1.0]"
      									],
      									"x" : [
      										"[MinKey, MaxKey]"
      									]
      								}
      							},
      							{
      								"stage" : "IXSCAN",
      								"keyPattern" : {
      									"mbox_id" : 1,
      									"x" : 1
      								},
      								"indexName" : "mbox_id_1_x_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"mbox_id" : [
      										"[1.0, 1.0]"
      									],
      									"x" : [
      										"[MinKey, MaxKey]"
      									]
      								}
      							},
      							{
      								"stage" : "IXSCAN",
      								"keyPattern" : {
      									"mbox_id" : 1,
      									"x" : 1
      								},
      								"indexName" : "mbox_id_1_x_1",
      								"isMultiKey" : true,
      								"direction" : "forward",
      								"indexBounds" : {
      									"mbox_id" : [
      										"[1.0, 1.0]"
      									],
      									"x" : [
      										"[MinKey, MaxKey]"
      									]
      								}
      							}
      						]
      					}
      				}
      			}
      		]
      	},
      	"serverInfo" : {
      		"host" : "AD-MAC10G-2.local",
      		"port" : 27017,
      		"version" : "3.0.7",
      		"gitVersion" : "6ce7cbe8c6b899552dadd907604559806aa2e9bd"
      	},
      	"ok" : 1
      }
      

      It appears that when we self-intersect a compound index we don't use the second field. Is that correct? If so, is it a known and understood limitation?

        Attachments

          Activity

            People

            Assignee:
            backlog-query-optimization Backlog - Query Optimization
            Reporter:
            alex.komyagin Alexander Komyagin
            Participants:
            Votes:
            0 Vote for this issue
            Watchers:
            14 Start watching this issue

              Dates

              Created:
              Updated: