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
- Does
#elem_match
not respect indexes or#hint
? - Is there a different way to get it to use the right index when matching on embedded documents?
- What am I missing here?
Aucun commentaire:
Enregistrer un commentaire