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

Sometimes QueryPlan use wrong index

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major - P3
    • Resolution: Duplicate
    • Affects Version/s: 2.6.8, 3.0.1
    • Fix Version/s: None
    • Component/s: Indexing, Querying
    • Labels:
      None
    • Environment:
      Ubuntu 14.04.2 LTS 64bit
    • Operating System:
      Linux

      Description

      Hello.

      I Have 20 nodes in cluster. Sometimes random node freeze due to begin use wrong indexe for query.

      Example:

      2015-03-20T15:23:01.646+0100 I QUERY    [conn2572] query goods.events query: { $query: { gt: ObjectId('53216161688fcfc9fed24755'), act: "attr", lang: "RU", ta : "name", st: 1 }, $readPreference: { mode: "nearest" } } planSummary: IXSCAN { act: 1, c: -1 } ntoskip:0 nscanned:36885811 nscannedObjects:36885811 keyUpdate s:0 writeConflicts:0 numYields:295880 nreturned:1 reslen:382 locks:{} 29456ms
      

      Usually this query use "gt_act_lang_ta" index.

      events0003:PRIMARY> db.getSiblingDB('goods').getCollection('events').getIndexes()
      [
              {
                      "v" : 1,
                      "key" : {
                              "_id" : 1
                      },
                      "name" : "_id_",
                      "ns" : "goods.events"
              },
              {
                      "v" : 1,
                      "key" : {
                              "u" : -1
                      },
                      "name" : "updated",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "tn" : 1
                      },
                      "name" : "tn",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "c" : -1
                      },
                      "name" : "created",
                      "ns" : "goods.events",
                      "background" : false
              },
              {
                      "v" : 1,
                      "key" : {
                              "t" : 1,
                              "c" : -1
                      },
                      "name" : "type_create",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "act" : 1,
                              "c" : -1
                      },
                      "name" : "act_create",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "ut" : 1,
                              "act" : 1,
                              "u" : 1
                      },
                      "name" : "ut_act_u",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "ntfy" : 1
                      },
                      "name" : "ntfy_1",
                      "ns" : "goods.events",
                      "sparse" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "ut2" : 1,
                              "act" : 1,
                              "u" : -1
                      },
                      "name" : "ut2_1_act_1_u_-1",
                      "ns" : "goods.events",
                      "sparse" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "gt" : 1,
                              "act" : 1,
                              "lang" : 1,
                              "ta" : 1
                      },
                      "name" : "gt_act_lang_ta",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "act" : 1,
                              "ta" : 1,
                              "c" : 1
                      },
                      "name" : "act_ta_c",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "exist" : 1,
                              "act" : 1,
                              "ut" : 1
                      },
                      "name" : "exist_act_ut",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "ut" : 1,
                              "u" : 1
                      },
                      "name" : "ut_u",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "unique" : true,
                      "key" : {
                              "gt" : 1,
                              "ut" : 1,
                              "act" : 1,
                              "ta" : 1,
                              "lang" : 1
                      },
                      "name" : "gt_1_ut_1_act_1_ta_1_lang_1",
                      "ns" : "goods.events",
                      "background" : true
              },
              {
                      "v" : 1,
                      "key" : {
                              "gt" : 1,
                              "act" : 1,
                              "geo" : "2dsphere"
                      },
                      "name" : "gt_1_act_1_geo_2dsphere",
                      "ns" : "goods.events",
                      "sparse" : true,
                      "background" : true,
                      "2dsphereIndexVersion" : 2
              }
      ]
      

        Attachments

        1. out.txt
          27 kB
        2. out2.txt
          9 kB

          Issue Links

            Activity

              People

              Assignee:
              david.storch David Storch
              Reporter:
              esp1974 Dmitry
              Participants:
              Votes:
              0 Vote for this issue
              Watchers:
              8 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: