[SERVER-23561] Distinct on sub-documents array doesn't use index Created: 06/Apr/16  Updated: 16/Nov/21  Resolved: 06/Apr/16

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

Type: Bug Priority: Major - P3
Reporter: Tom Grossman Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Related
related to SERVER-2104 covered index should support dotted f... Closed
is related to SERVER-23582 Query planner should not generate DIS... Closed
Operating System: ALL
Steps To Reproduce:
  1. Create doc with sub-docs array
  2. Create an index on one of the sub-docs fields
  3. Distinct on that field
Participants:

 Description   

If there is an index on a field in an array of sub-documents, distinct on this field doesn't use the index.

For example, I have the following collection schema:

{
    "arr" : [ 
        {
            "field_1" : 1,
            "field_2" : 2
        }, 
        {
            "field_1" : 11,
            "field_2" : 22
        }
    ]
}

And I have the following index:

{
    "arr.field_1" : 1
}

If I run the following command:

db.runCommand(
    {
        distinct: 'test',
        key: 'arr.field_1'
    }
)

I can see that the planSummary uses COLLSCAN and not DISTINCT_SCAN as I expected.



 Comments   
Comment by J Rassi [ 06/Apr/16 ]

Unfortunately, MongoDB doesn't support covered queries on dotted fields at the moment. Support for this feature is being tracked at SERVER-2104. DISTINCT_SCAN plans are special query plans optimized for distinct, which use a covered query against an index compatible with the distinct field. Thus, if the distinct field is dotted, DISTINCT_PLANS can't be generated. I'm closing this ticket as a dup of SERVER-2104; please add yourself as a watcher of that ticket for further updates.

On a related note, I've discovered while investigating this issue that MongoDB does incorrectly generate DISTINCT_SCAN plans for dotted fields if the index is not multi-key. I've filed SERVER-23582 to fix this.

Generated at Thu Feb 08 04:03:45 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.