mardi 21 avril 2015

elem_match not using indexes on embedded documents?

I have a massive collection of Person documents that have embedded arrays of Extension documents, with an index on 2 fields in the Extension. My find operations are going extremely slowly and it seems like my query is not using the right index even when using #hint.

Example Person doc:

{
  name: 'John Doe',
  organization_id: ObjectId('abc123')
  extensions: [
    {type: 'Address', city: 'Omaha', voter: false}
  ]
}

Indexes on this collection are on:

  • organization_id
  • composite of "extensions.type" and "extensions.city"

When I try to run a query or do an explain, or even force it to use the second index, my query hangs (my guess is it's doing a table scan or at least going through everything under organization_id, which could include millions of rows).

# Originally this was scoped to the specific organization
#   organization.people.elem_match
# but I tried just doing it on the class to try to get it on the 2nd index
People.elem_match(extensions: 
  {type: 'Address', city: 'Omaha', voter: false}
).one

I even tried to force it to use the second index via #hint

People.elem_match(extensions: 
  {type: 'Address', city: 'Omaha', voter: false}
).hint('extensions.type' => 1, 'extensions.city' => 1).one

Questions

  1. Does #elem_match not respect indexes or #hint?
  2. Is there a different way to get it to use the right index when matching on embedded documents?
  3. What am I missing here?

Aucun commentaire:

Enregistrer un commentaire