indexes on sub-documents, and $exists --- does $exists prevent the index on that column?

[Mark]

To explain the stuff below:

First is a query returning 10 rows but scanning 8000. 

Then, I force the index, no help. 

Then I take off the "exists" condition, and it returns the same no of rows. 

So I was thinking the $exists function is doing a null modify on the column and is somehow aborting the index on the sub documents --- on the column it checks. So, $exists will abort the column it checks for indexes. 

So, does $exists abort the column in the index you are checking for?

If so, how would you detect if the sub-document exists and be able to use the index at the same time for the sub-document?

Thanks!

Mark

m01:SECONDARY> db.E.find({"uId" : "u85694","$or" : [ {"a.u" : {"$exists" : true}}]}).explain();  

{

        "cursor" : "BtreeCursor uId_1",

        "nscanned" : 8322,

        "nscannedObjects" : 8322,

        "n" : 10,

        "millis" : 17865,

        "nYields" : 0,

        "nChunkSkips" : 0,

        "isMultiKey" : false,

        "indexOnly" : false,

        "indexBounds" : {"uId" : [ [    "u85694","u85694"       ]]}

}

m01:SECONDARY> db.E.find({"uId" : "u85694","$or" : [ {"a.u" : {"$exists" : true}}]}).count()

10

m01:SECONDARY> db.E.find({"uId" : "u85694","$or" : [

{"a.u" : {"$exists" :

true}}]}).hint("uId_1_a.u_1").explain();

{

       "cursor" : "BtreeCursor uId_1_a.u_1",

       "nscanned" : 8322,

       "nscannedObjects" : 8322,

       "n" : 8322,

       "millis" : 13,

       "nYields" : 0,

       "nChunkSkips" : 0,

       "isMultiKey" : false,

       "indexOnly" : false,

       "indexBounds" : {

               "uId" : [ ["u85694", "u85694" ]],

               "a.u" : [ [ { "$minElement" : 1}, { "$maxElement" : 1}   ]  ]

       }

}

m01:SECONDARY> db.E.find({"uId" :

"u85694"}).explain();

{

       "cursor" : "BtreeCursor uId_1",

       "nscanned" : 8322,

       "nscannedObjects" : 8322,

       "n" : 8322,

       "millis" : 11,

       "nYields" : 0,

       "nChunkSkips" : 0,

       "isMultiKey" : false,

       "indexOnly" : false,

       "indexBounds" : { "uId" : [ ["u85694", "u85694"  ] ] }

}

m01:SECONDARY> db.E.find({"uId" : "u85694"}).count();

8322

[Max Schireson]

it looks like 8k records match the first part of the query but only 10
match the entire query. there is an index on uId but not on a.u so
those records need to be scanned.

you might try creating a compound index on { uId:1, "a.u":1 } then i
think it should be able to check both the equality and $exists out of
index.

-- Max

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

[Max Schireson]

Sorry my bad you tried that already and hinted it in the second
example and it did not behave as expected.

I am not sure what is up I will take a look.

-- Max

[Max Schireson]

Ok, two part answer.

1. For $exists: true the index doesn't reduce the number of scanned objects because the index entry for an explicit null and $exists: false look the same so it has to scan the object to determine if it is an explicit null or just doesn't exist. That lack of additinal selectivity from the compound index is likely why it wasn't selected without the hint even though intuitively it seemed like it would help. The compound index should help with $ne: null but the semantic is slightly different - would that work for your app?

2. As to why more records are returned with the hinted index, I suspect a bug. What version of mongodb are you running?

-- Max

[Max Schireson]

Looking at another issue you were discussing with Scott it appears you are running 1.8. If this issue occured on 1.8 as well, then you hit some issues around $exists and indexes and ors. Your query might work without the $or (assuming the $exists is the only other clause) but I'd recommend an upgrade to 2.0 anyhow if possible.

If you're curious this looks like the fix:

https://github.com/mongodb/mongo/commit/ f97863227992f5e25ebe0882875d54cc5cc5937a

Hope this helps and sorry for the false start I missed the middle query with the hint entirely which was the really interesting one!

-- Max

[Mark]

Thanks!

I will test out your suggestion, I think the $ne is the trick since it doesn't null modify the column. 

I am using 1.8, and I was wondering if 2.0 would solve the problem. 

We are in the process up upgrading, but how I do it depends on another quesiton I had about failovers. 

Mark

On Tuesday, May 15, 2012 12:21:10 AM UTC-7, Max Schireson wrote:

Looking at another issue you were discussing with Scott it appears you are running 1.8. If this issue occured on 1.8 as well, then you hit some issues around $exists and indexes and ors. Your query might work without the $or (assuming the $exists is the only other clause) but I'd recommend an upgrade to 2.0 anyhow if possible.

If you're curious this looks like the fix:

https://github.com/mongodb/mongo/commit/ f97863227992f5e25ebe0882875d54cc5cc5937a

Hope this helps and sorry for the false start I missed the middle query with the hint entirely which was the really interesting one!

-- Max

On May 14, 2012 11:55 PM, "Max Schireson" <> wrote:

Ok, two part answer.

1. For $exists: true the index doesn't reduce the number of scanned objects because the index entry for an explicit null and $exists: false look the same so it has to scan the object to determine if it is an explicit null or just doesn't exist. That lack of additinal selectivity from the compound index is likely why it wasn't selected without the hint even though intuitively it seemed like it would help. The compound index should help with $ne: null but the semantic is slightly different - would that work for your app?

2. As to why more records are returned with the hinted index, I suspect a bug. What version of mongodb are you running?

-- Max

On May 14, 2012 9:53 PM, "Max Schireson" <> wrote:

Sorry my bad you tried that already and hinted it in the second
example and it did not behave as expected.

I am not sure what is up I will take a look.

-- Max

[Max Schireson]

2.0 should solve, and $ne: null will also likely avoid the issues -
1.8 had some funky issues around exists queries specifically, the
engineer on that area (who helped me figure this out) things $ne: null
will be ok both for performance and avoiding the bug.

-- Max