Hi,

I don't understand why a query scanned too much data, despite the use of
an index.

Thanks for your help,

Here is the index :
      {
          "v" : 1,
          "key" : {
              "date" : 1,
              "infos.sourceValue" : 1
          },
          "ns" : "analytics.aggregation",
          "name" : "date_1_infos.sourceValue_1"
      }


Here is the query :
      "query" : {
          "date" : ISODate("2012-11-12T23:00:00Z"),
          "infos" : {
              "sourceValue" : NumberLong(15),
              "eventType" : "read",
              "sourceType" : "user",
              "contextType" : "space",
              "instance" : "trunk",
              "contextValue" : NumberLong(3839),
              "targetType" : "message",
              "targetValue" : NumberLong(541545)
          }
      },

"nscanned" : 5798,
db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z")}) 5798
db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z"),
"infos.sourceValue":15})
42

--
--
You received this message because you are subscribed to the Google
Groups "mongodb-user" group.
To post to this group, send email to mongodb-user@googlegroups.com
To unsubscribe from this group, send email to
mongodb-user+unsubscribe@googlegroups.com
See also the IRC channel -- freenode.net#mongodb

---
You received this message because you are subscribed to the Google Groups "mongodb-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Search Discussions

  • Yann Moisan at Sep 4, 2013 at 1:56 pm
    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
         "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
         "isMultiKey" : false,
         "n" : 42,
         "nscannedObjects" : 42,
         "nscanned" : 42,
         "nscannedObjectsAllPlans" : 42,
         "nscannedAllPlans" : 42,
         "scanAndOrder" : false,
         "indexOnly" : false,
         "nYields" : 0,
         "nChunkSkips" : 0,
         "millis" : 0,
         "indexBounds" : {
             "date" : [
                 [
                     ISODate("2012-12-02T23:00:00Z"),
                     ISODate("2012-12-02T23:00:00Z")
                 ]
             ],
             "infos.sourceValue" : [
                 [
                     4263,
                     4263
                 ]
             ]
         },
         "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
         "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
         "isMultiKey" : false,
         "n" : 0,
         "nscannedObjects" : 5798,
         "nscanned" : 5798,
         "nscannedObjectsAllPlans" : 11596,
         "nscannedAllPlans" : 11596,
         "scanAndOrder" : false,
         "indexOnly" : false,
         "nYields" : 90,
         "nChunkSkips" : 0,
         "millis" : 169,
         "indexBounds" : {
             "date" : [
                 [
                     ISODate("2012-12-02T23:00:00Z"),
                     ISODate("2012-12-02T23:00:00Z")
                 ]
             ],
             "infos.sourceValue" : [
                 [
                     {
                         "$minElement" : 1
                     },
                     {
                         "$maxElement" : 1
                     }
                 ]
             ]
         },
         "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :
    Hi,

    I don't understand why a query scanned too much data, despite the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z")}) 5798
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Jeff Lee at Sep 4, 2013 at 9:39 pm
    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to use
    when specifying partial matches on subdocuments, otherwise you're trying to
    match the entire document and can't use the index.

    The docs on sub-documents<http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>may
    help.

    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :
    Hi,

    I don't understand why a query scanned too much data, despite the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"**),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":**new ISODate("2012-11-12T23:00:00Z"**)}) 5798
    db.aggregation.count({"date":**new ISODate("2012-11-12T23:00:00Z"**),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Yamo93 at Sep 5, 2013 at 9:58 am
    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting and I
    think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter document
    that don't match (i.e. reduce nscanned), even if I search entire match.

    Rgds,
    Yann.
    On 04/09/2013 23:38, Jeff Lee wrote:
    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to
    use when specifying partial matches on subdocuments, otherwise you're
    trying to match the entire document and can't use the index.

    The docs on sub-documents
    <http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>
    may help.


    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :

    Hi,

    I don't understand why a query scanned too much data, despite
    the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z")})
    5798
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to mongodb-user+unsubscribe@googlegroups.com
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Asya Kamsky at Sep 5, 2013 at 4:06 pm
    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match (i.e. reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying "foo.bar":5 means "foo" must contain a field "bar" which is 5 and saying "foo":{ "bar":5} means "foo" must be equal to *exactly* { "bar":5}

    This is completely independent of indexing.

    Asya


    -----Original Message-----
    From: yamo93 <yamo93@gmail.com>
    Sender: mongodb-user@googlegroups.com
    Date: Thu, 05 Sep 2013 11:56:39
    To: <mongodb-user@googlegroups.com>
    Reply-To: mongodb-user@googlegroups.com
    Subject: Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting and I
    think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter document
    that don't match (i.e. reduce nscanned), even if I search entire match.

    Rgds,
    Yann.
    On 04/09/2013 23:38, Jeff Lee wrote:
    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to
    use when specifying partial matches on subdocuments, otherwise you're
    trying to match the entire document and can't use the index.

    The docs on sub-documents
    <http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>
    may help.


    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :

    Hi,

    I don't understand why a query scanned too much data, despite
    the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z")})
    5798
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to mongodb-user+unsubscribe@googlegroups.com
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Asya Kamsky at Sep 5, 2013 at 4:18 pm
    Yann,

    Someone suggested that perhaps what you mean is that the
      "infos.sourceValue" part of the index could be used to rule out documents
    that don't match 4263 - I agree that would be a very clever optimization
    (then if the value is equal the document can be checked that this satisfies
    the full match).

    If there is not a jira for this, I think it would be worth filing - we're
    currently re-writing the optimizer completely and it's possible that this
    may be more feasible to implement in the new code.

    Asya


    On Thu, Sep 5, 2013 at 9:05 AM, Asya Kamsky wrote:

    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match (i.e.
    reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying
    "foo.bar":5 means "foo" must contain a field "bar" which is 5 and saying
    "foo":{ "bar":5} means "foo" must be equal to *exactly* { "bar":5}

    This is completely independent of indexing.

    Asya

    ------------------------------
    *From: * yamo93 <yamo93@gmail.com>
    *Sender: * mongodb-user@googlegroups.com
    *Date: *Thu, 05 Sep 2013 11:56:39 +0200
    *To: *<mongodb-user@googlegroups.com>
    *ReplyTo: * mongodb-user@googlegroups.com
    *Subject: *Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting and I
    think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive, because
    the index with dot notation could be used to filter document that don't
    match (i.e. reduce nscanned), even if I search entire match.

    Rgds,
    Yann.

    On 04/09/2013 23:38, Jeff Lee wrote:

    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to use
    when specifying partial matches on subdocuments, otherwise you're trying to
    match the entire document and can't use the index.

    The docs on sub-documents<http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>may help.

    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :
    Hi,

    I don't understand why a query scanned too much data, despite the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z")}) 5798
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Yamo93 at Sep 6, 2013 at 7:33 am
    That's exactly what I mean.
    I check today and if there is not a Jira, I'll create one.
    On 05/09/2013 18:18, Asya Kamsky wrote:
    Yann,

    Someone suggested that perhaps what you mean is that the
    "infos.sourceValue" part of the index could be used to rule out
    documents that don't match 4263 - I agree that would be a very clever
    optimization (then if the value is equal the document can be checked
    that this satisfies the full match).

    If there is not a jira for this, I think it would be worth filing -
    we're currently re-writing the optimizer completely and it's possible
    that this may be more feasible to implement in the new code.

    Asya



    On Thu, Sep 5, 2013 at 9:05 AM, Asya Kamsky wrote:

    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match
    (i.e. reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying
    "foo.bar":5 means "foo" must contain a field "bar" which is 5 and
    saying "foo":{ "bar":5} means "foo" must be equal to *exactly* {
    "bar":5}

    This is completely independent of indexing.

    Asya

    ------------------------------------------------------------------------
    *From: * yamo93 <yamo93@gmail.com *Sender: * mongodb-user@googlegroups.com
    *Date: *Thu, 05 Sep 2013 11:56:39 +0200
    *To: *<mongodb-user@googlegroups.com
    *ReplyTo: * mongodb-user@googlegroups.com
    *Subject: *Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting
    and I think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter
    document that don't match (i.e. reduce nscanned), even if I search
    entire match.

    Rgds,
    Yann.
    On 04/09/2013 23:38, Jeff Lee wrote:
    Dot notation is the ( infos.sourceValue ) is the appropriate
    syntax to use when specifying partial matches on subdocuments,
    otherwise you're trying to match the entire document and can't
    use the index.

    The docs on sub-documents
    <http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>
    may help.


    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan <yamo93@gmail.com
    wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" :
    ISODate("2012-12-02T23:00:00Z"), "infos.sourceValue" : 4263
    } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" :
    ISODate("2012-12-02T23:00:00Z"), "infos":{"sourceValue" :
    4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a
    écrit :

    Hi,

    I don't understand why a query scanned too much data,
    despite the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z")})
    5798
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the
    Google
    Groups "mongodb-user" group.
    To post to this group, send email to
    mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the
    Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from
    it, send an email to
    mongodb-user+unsubscribe@googlegroups.com

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to
    mongodb-user@googlegroups.com > To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to a topic
    in the Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email
    to mongodb-user+unsubscribe@googlegroups.com
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to mongodb-user+unsubscribe@googlegroups.com
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Yamo93 at Sep 10, 2013 at 8:07 am
    It seems that there is an old Jira (April 2011) for this :
    https://jira.mongodb.org/browse/SERVER-2953.
    On 05/09/2013 18:18, Asya Kamsky wrote:
    Yann,

    Someone suggested that perhaps what you mean is that the
    "infos.sourceValue" part of the index could be used to rule out
    documents that don't match 4263 - I agree that would be a very clever
    optimization (then if the value is equal the document can be checked
    that this satisfies the full match).

    If there is not a jira for this, I think it would be worth filing -
    we're currently re-writing the optimizer completely and it's possible
    that this may be more feasible to implement in the new code.

    Asya



    On Thu, Sep 5, 2013 at 9:05 AM, Asya Kamsky wrote:

    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match
    (i.e. reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying
    "foo.bar":5 means "foo" must contain a field "bar" which is 5 and
    saying "foo":{ "bar":5} means "foo" must be equal to *exactly* {
    "bar":5}

    This is completely independent of indexing.

    Asya

    ------------------------------------------------------------------------
    *From: * yamo93 <yamo93@gmail.com *Sender: * mongodb-user@googlegroups.com
    *Date: *Thu, 05 Sep 2013 11:56:39 +0200
    *To: *<mongodb-user@googlegroups.com
    *ReplyTo: * mongodb-user@googlegroups.com
    *Subject: *Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting
    and I think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter
    document that don't match (i.e. reduce nscanned), even if I search
    entire match.

    Rgds,
    Yann.
    On 04/09/2013 23:38, Jeff Lee wrote:
    Dot notation is the ( infos.sourceValue ) is the appropriate
    syntax to use when specifying partial matches on subdocuments,
    otherwise you're trying to match the entire document and can't
    use the index.

    The docs on sub-documents
    <http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>
    may help.


    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan <yamo93@gmail.com
    wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" :
    ISODate("2012-12-02T23:00:00Z"), "infos.sourceValue" : 4263
    } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" :
    ISODate("2012-12-02T23:00:00Z"), "infos":{"sourceValue" :
    4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a
    écrit :

    Hi,

    I don't understand why a query scanned too much data,
    despite the use of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z")})
    5798
    db.aggregation.count({"date":new
    ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the
    Google
    Groups "mongodb-user" group.
    To post to this group, send email to
    mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the
    Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from
    it, send an email to
    mongodb-user+unsubscribe@googlegroups.com

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to
    mongodb-user@googlegroups.com > To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to a topic
    in the Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email
    to mongodb-user+unsubscribe@googlegroups.com
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb
    <http://freenode.net#mongodb>

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to mongodb-user+unsubscribe@googlegroups.com
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Sam Millman at Sep 10, 2013 at 8:21 am
    Hmm wouldn't that JIRA actually break object comparison by converting
    everything forceably to be dot notation?

    That makes use of the index by breaking object comparison.

    On 10 September 2013 09:06, yamo93 wrote:

    It seems that there is an old Jira (April 2011) for this :
    https://jira.mongodb.org/browse/SERVER-2953.


    On 05/09/2013 18:18, Asya Kamsky wrote:

    Yann,

    Someone suggested that perhaps what you mean is that the
    "infos.sourceValue" part of the index could be used to rule out
    documents that don't match 4263 - I agree that would be a very clever
    optimization (then if the value is equal the document can be checked that
    this satisfies the full match).

    If there is not a jira for this, I think it would be worth filing -
    we're currently re-writing the optimizer completely and it's possible that
    this may be more feasible to implement in the new code.

    Asya


    On Thu, Sep 5, 2013 at 9:05 AM, Asya Kamsky wrote:

    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match (i.e.
    reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying
    "foo.bar":5 means "foo" must contain a field "bar" which is 5 and saying
    "foo":{ "bar":5} means "foo" must be equal to *exactly* { "bar":5}

    This is completely independent of indexing.

    Asya

    ------------------------------
    *From: * yamo93 <yamo93@gmail.com>
    *Sender: * mongodb-user@googlegroups.com
    *Date: *Thu, 05 Sep 2013 11:56:39 +0200
    *To: *<mongodb-user@googlegroups.com>
    *ReplyTo: * mongodb-user@googlegroups.com
    *Subject: *Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting and I
    think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter document that
    don't match (i.e. reduce nscanned), even if I search entire match.

    Rgds,
    Yann.

    On 04/09/2013 23:38, Jeff Lee wrote:

    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to
    use when specifying partial matches on subdocuments, otherwise you're
    trying to match the entire document and can't use the index.

    The docs on sub-documents<http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>may help.

    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :
    Hi,

    I don't understand why a query scanned too much data, despite the use
    of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z")}) 5798
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send
    an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Sam Millman at Sep 10, 2013 at 8:26 am

    To explain myself further:

    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()

    The fact that the index is not used there is a perfectly documented
    feature, it is because you are using object comparison instead of comparing
    on the parts of the subdocument using dot notation. Although rarely used I
    can imagine this feature is used at times.


    On 10 September 2013 09:21, Sam Millman wrote:

    Hmm wouldn't that JIRA actually break object comparison by converting
    everything forceably to be dot notation?

    That makes use of the index by breaking object comparison.

    On 10 September 2013 09:06, yamo93 wrote:

    It seems that there is an old Jira (April 2011) for this :
    https://jira.mongodb.org/browse/SERVER-2953.


    On 05/09/2013 18:18, Asya Kamsky wrote:

    Yann,

    Someone suggested that perhaps what you mean is that the
    "infos.sourceValue" part of the index could be used to rule out
    documents that don't match 4263 - I agree that would be a very clever
    optimization (then if the value is equal the document can be checked that
    this satisfies the full match).

    If there is not a jira for this, I think it would be worth filing -
    we're currently re-writing the optimizer completely and it's possible that
    this may be more feasible to implement in the new code.

    Asya


    On Thu, Sep 5, 2013 at 9:05 AM, Asya Kamsky wrote:

    I'm not clear about your last point - what do you mean by

    "dot notation could be used to filter document that don't match (i.e.
    reduce nscanned), even if I search entire match."

    The point is that when you specify your query condition, saying
    "foo.bar":5 means "foo" must contain a field "bar" which is 5 and saying
    "foo":{ "bar":5} means "foo" must be equal to *exactly* { "bar":5}

    This is completely independent of indexing.

    Asya

    ------------------------------
    *From: * yamo93 <yamo93@gmail.com>
    *Sender: * mongodb-user@googlegroups.com
    *Date: *Thu, 05 Sep 2013 11:56:39 +0200
    *To: *<mongodb-user@googlegroups.com>
    *ReplyTo: * mongodb-user@googlegroups.com
    *Subject: *Re: [mongodb-user] Re: Query scan too much data

    Thanks for your answer, I understand now.

    I have read the documentation that you mentioned before posting and I
    think that your explication doesn't appear clearly in the doc.

    Moreover, I find this behaviour of MongoDB a bit counterintuitive,
    because the index with dot notation could be used to filter document that
    don't match (i.e. reduce nscanned), even if I search entire match.

    Rgds,
    Yann.

    On 04/09/2013 23:38, Jeff Lee wrote:

    Dot notation is the ( infos.sourceValue ) is the appropriate syntax to
    use when specifying partial matches on subdocuments, otherwise you're
    trying to match the entire document and can't use the index.

    The docs on sub-documents<http://docs.mongodb.org/manual/tutorial/query-documents/#read-operations-subdocuments>may help.

    On Wed, Sep 4, 2013 at 6:56 AM, Yann Moisan wrote:

    I have much information on the problem.

    This one works fine (i.e. use the index as expected) :
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos.sourceValue" : 4263 } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 42,
    "nscannedObjects" : 42,
    "nscanned" : 42,
    "nscannedObjectsAllPlans" : 42,
    "nscannedAllPlans" : 42,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 0,
    "nChunkSkips" : 0,
    "millis" : 0,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    4263,
    4263
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    And this one not (i don't understand why)
    db.aggregation.find({"date" : ISODate("2012-12-02T23:00:00Z"),
    "infos":{"sourceValue" : 4263 } } ).explain()
    {
    "cursor" : "BtreeCursor date_1_infos.sourceValue_1",
    "isMultiKey" : false,
    "n" : 0,
    "nscannedObjects" : 5798,
    "nscanned" : 5798,
    "nscannedObjectsAllPlans" : 11596,
    "nscannedAllPlans" : 11596,
    "scanAndOrder" : false,
    "indexOnly" : false,
    "nYields" : 90,
    "nChunkSkips" : 0,
    "millis" : 169,
    "indexBounds" : {
    "date" : [
    [
    ISODate("2012-12-02T23:00:00Z"),
    ISODate("2012-12-02T23:00:00Z")
    ]
    ],
    "infos.sourceValue" : [
    [
    {
    "$minElement" : 1
    },
    {
    "$maxElement" : 1
    }
    ]
    ]
    },
    "server" : "dev-bluekarma02:27017"
    }


    Le mercredi 4 septembre 2013 12:02:18 UTC+2, Yann Moisan a écrit :
    Hi,

    I don't understand why a query scanned too much data, despite the use
    of
    an index.

    Thanks for your help,

    Here is the index :
    {
    "v" : 1,
    "key" : {
    "date" : 1,
    "infos.sourceValue" : 1
    },
    "ns" : "analytics.aggregation",
    "name" : "date_1_infos.sourceValue_1"
    }


    Here is the query :
    "query" : {
    "date" : ISODate("2012-11-12T23:00:00Z"),
    "infos" : {
    "sourceValue" : NumberLong(15),
    "eventType" : "read",
    "sourceType" : "user",
    "contextType" : "space",
    "instance" : "trunk",
    "contextValue" : NumberLong(3839),
    "targetType" : "message",
    "targetValue" : NumberLong(541545)
    }
    },

    "nscanned" : 5798,
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z")}) 5798
    db.aggregation.count({"date":new ISODate("2012-11-12T23:00:00Z"),
    "infos.sourceValue":15})
    42

    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send
    an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send
    an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to a topic in the
    Google Groups "mongodb-user" group.
    To unsubscribe from this topic, visit
    https://groups.google.com/d/topic/mongodb-user/a-ln0OnbTaM/unsubscribe.
    To unsubscribe from this group and all its topics, send an email to
    mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.


    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups
    "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an
    email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
    --
    --
    You received this message because you are subscribed to the Google
    Groups "mongodb-user" group.
    To post to this group, send email to mongodb-user@googlegroups.com
    To unsubscribe from this group, send email to
    mongodb-user+unsubscribe@googlegroups.com
    See also the IRC channel -- freenode.net#mongodb

    ---
    You received this message because you are subscribed to the Google Groups "mongodb-user" group.
    To unsubscribe from this group and stop receiving emails from it, send an email to mongodb-user+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupmongodb-user @
categoriesmongodb
postedSep 4, '13 at 10:04a
activeSep 10, '13 at 8:26a
posts10
users5
websitemongodb.org
irc#mongodb

People

Translate

site design / logo © 2022 Grokbase