[SERVER-128] Index not used in Multikeys with $in operator Created: 06/Jul/09  Updated: 30/Jul/09  Resolved: 06/Jul/09

Status: Closed
Project: Core Server
Component/s: Index Maintenance
Affects Version/s: 0.9.5
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Kirill Mavreshko Assignee: Eliot Horowitz (Inactive)
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu Server 9.04, 768 RAM, fresh installation of MongoDB 0.9.5, pymongo connector


Participants:

 Description   

I create simple test similar described in docs (http://www.mongodb.org/display/DOCS/Multikeys) for problem demonstration:
My comment added in output with "<<<===" construction.
-------------------
from pymongo.connection import Connection
from pprint import pprint

connection = Connection("localhost", 27017)
db = connection.test

db.create_collection("example")

  1. example list is [[1,2,3,4,5],[4,5,6,7,8],[7,8,9,10,11],[10,11,12,13,14]...]
    samples = [range(i, i+5) for i in xrange(1, 10000, 3)]

for item in samples:
db.example.save(

{"test":item}

)

db.example.ensure_index("test",1)
query = {"test":{"$in":[1,100,9000]}}

print "Result:"
pprint(list(db.example.find(query)))

print "\nQuery Explain:"
explain = db.example.find(query).explain()
pprint(dict(explain.items()))
----------------------------

After run it, I receive explain output:
...<skipped>...
{u'allPlans': [{u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'startKey': {u'test': 1}}],
u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'millis': 25,
u'n': 4,
u'nscanned': 14998.0, <<<====== Why 'nscanned' so large?! With index!
u'oldPlan': {u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'startKey': {u'test': 1}},
u'startKey': {u'test': 1}}
-------------

When I try to get records without IN

explain = db.example.find(

{"test":9000}

).explain()
pprint(dict(explain.items()))

result look as:

{u'allPlans': [{u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'startKey': {u'test': 9000}}],
u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'millis': 0, <<<========== very fast
u'n': 1,
u'nscanned': 1.0,
u'oldPlan': {u'cursor': u'BtreeCursor test_1',
u'endKey':

{u'test': 9000}

,
u'startKey': {u'test': 9000}},
u'startKey': {u'test': 9000}}

It's a normal behavior?



 Comments   
Comment by Eliot Horowitz (Inactive) [ 30/Jul/09 ]

closed since resolved and no activity in 2 weeks.
re-open if still issues.

Comment by Eliot Horowitz (Inactive) [ 06/Jul/09 ]

It uses the index, but right now is stupid and looks at the entire range.
We've got another case open to fix it SERVER-100 you could watch that.

Generated at Thu Feb 08 02:53:06 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.