[SERVER-14225] Compound Index is not working as expected Created: 11/Jun/14  Updated: 10/Dec/14  Resolved: 14/Jul/14

Status: Closed
Project: Core Server
Component/s: Querying
Affects Version/s: 2.6.1
Fix Version/s: None

Type: Question Priority: Major - P3
Reporter: Andre Mantei Assignee: David Storch
Resolution: Done Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Participants:

 Description   

Hi,

I have a collection that contains Dokuments in the following fashion:
Example:

{                                                                  
        "_id" : ObjectId("538846f9e5528f015c5d9f36"),              
        "id" : "257551252",                                        
        "amenity" : "restaurant",                                  
        "name" : "Clubheim RSV Seelze",                            
        "note" : "FIXME: Track please",                            
        "opening_hours" : "Mo-Fr 17:00-22:00; Sa-Su 10:00-19:00",  
        "operator" : "Ute Gleue",                                  
        "wheelchair" : "no",                                       
        "geo" : {                                                  
                "type" : "Point",                                  
                "coordinates" : [                                  
                        9.6184687,                                 
                        52.393087                                  
                ]                                                  
        }                                                          
}    

The following indexes exists:

       
> db.nodesWays.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "h171.nodesWays"
        },
        {
                "v" : 1,
                "key" : {
                        "geo" : "2dsphere"
                },
                "name" : "geo_2dsphere",
                "ns" : "h171.nodesWays",
                "2dsphereIndexVersion" : 2
        },
        {
                "v" : 1,
                "key" : {
                        "geo" : "2dsphere",
                        "amenity" : 1
                },
                "name" : "geo_2dsphere_amenity_1",
                "ns" : "h171.nodesWays",
                "2dsphereIndexVersion" : 2
        },
        {
                "v" : 1,
                "key" : {
                        "amenity" : 1,
                        "geo" : "2dsphere"
                },
                "name" : "amenity_1_geo_2dsphere",
                "ns" : "h171.nodesWays",
                "sparse" : true,
                "2dsphereIndexVersion" : 2
        }
]

When I query the collection with a hint to different indexes, it gives back different results. I would not expect that:

> db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}} , "amenity":"restaurant", name: { $exists: true }}, {id:1, name:1}).hint( "geo_2dsphere" ).limit(10)
{ "_id" : ObjectId("538846fae5528f015c5db6e7"), "id" : "321256694", "name" : "Masa" }
{ "_id" : ObjectId("538846fae5528f015c5db8c9"), "id" : "323101271", "name" : "Bavarium" }
{ "_id" : ObjectId("538846fae5528f015c5dcdaa"), "id" : "442496282", "name" : "Naxos" }
{ "_id" : ObjectId("538846fae5528f015c5db8bc"), "id" : "323101189", "name" : "Block House" }
{ "_id" : ObjectId("53884704e5528f015c5ebcec"), "id" : "2453236451", "name" : "Maestro" }
{ "_id" : ObjectId("538846ffe5528f015c5e4eb9"), "id" : "1992166428", "name" : "Weinstube Leonardo Ristorante" }
{ "_id" : ObjectId("538846fce5528f015c5e0215"), "id" : "1440320284", "name" : "Altdeutsche küche" }
{ "_id" : ObjectId("538846fae5528f015c5dc190"), "id" : "353119010", "name" : "Mövenpick" }
{ "_id" : ObjectId("538846f9e5528f015c5da371"), "id" : "265546900", "name" : "Miles" }
{ "_id" : ObjectId("538846fae5528f015c5dd402"), "id" : "532304135", "name" : "Globetrotter" }
 
> db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}}
... , "amenity":"restaurant", name: { $exists: true }}, {id:1, name:1}).hint( "amenity_1_geo_2dsphere" ).limit(10)
{ "_id" : ObjectId("538846fae5528f015c5dc9d2"), "id" : "412750264", "name" : "Bei Baki" }
{ "_id" : ObjectId("538846fae5528f015c5dc9e4"), "id" : "413318427", "name" : "Il Tricolore" }
{ "_id" : ObjectId("538846fce5528f015c5dfedc"), "id" : "1328325103", "name" : "Restaurant Steiner" }
{ "_id" : ObjectId("538846fce5528f015c5dfedb"), "id" : "1328316771", "name" : "Restaurant Hubertus" }
{ "_id" : ObjectId("5388470fe5528f015c606bb4"), "id" : "259261557", "name" : "Rudolf's Inn - Whisky & Zigarren-Club" }
{ "_id" : ObjectId("538846fae5528f015c5dc9d7"), "id" : "412761736", "name" : "Sambergers" }
{ "_id" : ObjectId("538846fbe5528f015c5de536"), "id" : "940735501", "name" : "Kleifeldstuben" }
{ "_id" : ObjectId("538846fae5528f015c5dce3f"), "id" : "448138789", "name" : "Taverne i spilia; Griechisch" }
{ "_id" : ObjectId("538846fae5528f015c5ddb0d"), "id" : "670501894", "name" : "Zum weißen Roß" }
{ "_id" : ObjectId("5388470de5528f015c602425"), "id" : "202011249", "name" : "Wülferode West" }

The first query gives back the correct result. Why is this happening ?

Bye, Andre



 Comments   
Comment by David Storch [ 25/Jun/14 ]

Hi andrempunkt,

I haven't heard from you in a while, so I just wanted to ping you again. Is there any chance that you would be able to provide the output from the commands that I pasted above? Also, can you reproduce the issue against both version 2.6.1 and version 2.6.3?

Thanks very much for your help,

Dave

Comment by David Storch [ 11/Jun/14 ]

Hi andrempunkt,

In addition to the .explain(true) output that Ramon mentioned, another thing that might help us to diagnose and reproduce the issue is if you paste the output without the projection. So, all together, could you please provide the output of the following four commands?

// 1. Hinting the first index, with explain.
db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}} , "amenity":"restaurant", name: { $exists: true }}, {id:1, name:1}).hint( "geo_2dsphere" ).limit(10).explain(true);
 
// 2. Hinting the second index, with explain.
db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}} , "amenity":"restaurant", name: { $exists: true }}, {id:1, name:1}).hint( "amenity_1_geo_2dsphere" ).limit(10).explain(true);
 
// 3. Hinting the first index, without the projection.
db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}} , "amenity":"restaurant", name: { $exists: true }}).hint( "geo_2dsphere" ).limit(10);
 
// 4. Hinting the second index, without the projection.
db.nodesWays.find({geo:{$nearSphere:{$geometry:{type: "Point", coordinates:  [9.7399777,52.3715156]}}} , "amenity":"restaurant", name: { $exists: true }}).hint( "amenity_1_geo_2dsphere" ).limit(10);

These results will allow us to both see what query plan is being used, and see the coordinates of the results that are being returned.

Thanks,
Dave

Comment by Ramon Fernandez Marina [ 11/Jun/14 ]

Hi andrempunkt,

can you please post the output of your two find() queries above with .explain(true) appended to them?

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