[SERVER-31100] Since 2.6, full collection scan instead of index scan, on range after sort Created: 15/Sep/17  Updated: 09/Oct/17  Resolved: 15/Sep/17

Status: Closed
Project: Core Server
Component/s: Index Maintenance, Querying
Affects Version/s: 2.6.12, 3.0.15, 3.2.16, 3.4.9, 3.5.13
Fix Version/s: None

Type: Bug Priority: Major - P3
Reporter: Clement R Assignee: Kelsey Schubert
Resolution: Duplicate Votes: 0
Labels: compound_index, index, sorting
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-13197 Tighten index bounds and allow compou... Backlog
Operating System: ALL
Steps To Reproduce:
Steps :
  1. Install docker :

    curl -fsSL get.docker.com -o get-docker.sh
    sh get-docker.sh

  2. (cli 1) Start a standalone mongod v2.2 :

    docker run --rm --name some-mongo mongo:2.2 --smallfiles --nojournal

  3. (cli 2) Open a mongo shell to this mongod :

    docker exec -it some-mongo "mongo"

  4. (cli 2) Create index, data, and find explain :

    db.createCollection('testIndex')
    db.testIndex.ensureIndex({a:1,b:1,c:1})
    for(var i=1;i++<500;){db.testIndex.insert({a:i,b:4,c:i+5});}
    for(var i=1;i++<500;){db.testIndex.insert({a:i,b:6,c:i+5});}
    db.testIndex.find({c:{$lt:100}}).sort({a:1,b:1}).explain()       # do an explain("executionStats") instead since v3.0 #
    

  5. (cli 1) Stop and remove this mongod with a [Ctrl]+[C]
  6. Start again at step 2, with each mongod version ("mongo:2.4", "mongo:2.6", "mongo:3.0", "mongo:3.2", "mongo:3.4", "mongo:3.5", etc).
  7. Optionally, clean un-used docker images to free disk space :

    docker rmi $(docker images -q)

Results :
mongod version totalKeysExamined (nscanned) totalDocsExamined (nscannedObjects) nReturned (n)
2.2.7 942 186 186
2.4.14 942 186 186
2.6.12 998 998 186
3.0.15 998 998 186
3.2.16 998 998 186
3.4.9 998 998 186
3.5.13 998 998 186
More details :
  • In 2.2 and 2.4, "indexBounds" looks something like this :

    "indexBounds" : {
        "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "c" : [ [ -1.7976931348623157e+308, 100 ] ]
    },
    

  • But in 2.6 an above :

    "indexBounds" : {
        "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "c" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ]
    },
    

Participants:

 Description   

Hello,

index {a:1,b:1,c:1}
find( {c:{$lt:100}} ).sort( {a:1,b:1} )

Before 2.6 : all matching docs are found from index (probably by doing an index scan of part "c" ?)
From 2.6 and above : index return docs in sorted order, but a full document scan is required to check "c".



 Comments   
Comment by Clement R [ 18/Sep/17 ]

Thanks for your reply.
I have little hope a 2014 bug report will ever be fixed, but their workaround works great :

If I modify my query from :

  • find( {c:{$lt:100}} ).sort( {a:1,b:1} )
    to
  • find({ c:{$lt:100}, a:{$gt:MinKey,$lt:MaxKey} }).sort( {a:1,b:1} )

explain() is good again in 2.6+ :

  • totalDocsExamined (nscannedObjects) : 186
  • still no in-memory sort
  • indexBounds looks much better :

    "indexBounds" : {
        "a" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "b" : [ [ { "$minElement" : 1 }, { "$maxElement" : 1 } ] ],
        "c" : [ [ -Infinity, 100 ] ]
    },
    

Kind regards,
Clement

Comment by Kelsey Schubert [ 15/Sep/17 ]

Hi clement@compilatio,

Thank you for the detailed bug report. The work to resolve this issue is tracked in SERVER-13197. Please feel free to vote for SERVER-13197 and watch it for updates

For additional details, please refer to Dave's comment on SERVER-28957, which more closely matches the issue you've reported.

Kind regards,
Kelsey

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