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

$elemMatch index scanning large numbers of rows

    XMLWordPrintableJSON

Details

    • Icon: Bug Bug
    • Resolution: Incomplete
    • Icon: Major - P3 Major - P3
    • None
    • 1.4.2
    • Querying
    • None

    Description

      I have an index on multiple columns - the last of which is a sort. The penultimate column in the index has very low cardinality - say 3,000 out of 5m records, is unlikely to go above 5,000 or so. When running an explain, the index is used, but mongo is doing a scan on 240k records to find out that only 343 of them have @fields.current.value : 1, despite that being in the index. If I remove that column from the index, I get exactly the same results as if it's there. However removing the column from the query it runs about three times faster. To me it seems like that part of the index ought to be used.

      Explain:

      > db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

      { 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1, 'current.value': 1 }

      } }).sort(

      { '@fields.pageviews.value': -1 }

      ).hint('content_list_homepage_popular_index').explain();
      {
      "cursor" : "BtreeCursor content_list_homepage_popular_index",
      "indexBounds" : [
      [
      {
      "@fields.edition.tid" : 369,
      "@fields.ex_node_group.value" : 1,
      "status" : 1,
      "@fields.current.value" : 1,
      "@fields.pageviews.value" :

      { "$maxElement" : 1 }

      },
      {
      "@fields.edition.tid" : 369,
      "@fields.ex_node_group.value" : 1,
      "status" : 1,
      "@fields.current.value" : 1,
      "@fields.pageviews.value" :

      { "$minElement" : 1 }

      }
      ]
      ],
      "nscanned" : 240011,
      "nscannedObjects" : 240011,
      "n" : 343,
      "millis" : 1474
      }
      > db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

      { 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1, 'current.value': 1 }

      } }).count();
      343
      > db.fields_current.node.find({ 'status': 1, '@fields': { '$elemMatch':

      { 'ex_node_group.value': 1, 'edition.tid': 369, 'channel.tid': 1}

      } }).count();
      64545
      >

      Attachments

        Activity

          People

            aaron Aaron Staple
            catch Nathaniel Catchpole
            Votes:
            4 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: