Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-29449

Explain of find command does not transform query for shards

XMLWordPrintableJSON

    • Query Optimization
    • ALL
    • Hide

      Starting with a sharded collection, in this example, the people.json from MongoDB University:

      mongos> db.version();
3.4.4
mongos> db.people.find().itcount();
50474
mongos> db.people.find().skip(1000).itcount();
49474
mongos> var stats = db.people.find().explain("executionStats");
mongos> print("nReturned: " + stats.executionStats.nReturned);var total=0;stats.executionStats.executionStages.shards.forEach(function(s){print(s.shardName+" nReturned: "+s.executionStages.nReturned); total += s.executionStages.nReturned;}); print("Sum of shards: " + total);
nReturned: 50474
shard0000 nReturned: 12130
shard0001 nReturned: 13125
shard0002 nReturned: 25219
Sum of shards: 50474
mongos> var stats = db.people.find().skip(1000).explain("executionStats");
mongos> print("nReturned: " + stats.executionStats.nReturned);var total=0;stats.executionStats.executionStages.shards.forEach(function(s){print(s.shardName+" nReturned: "+s.executionStages.nReturned); total += s.executionStages.nReturned;}); print("Sum of shards: " + total);
nReturned: 47474
shard0000 nReturned: 11130
shard0001 nReturned: 12125
shard0002 nReturned: 24219
Sum of shards: 47474
      Show
      Starting with a sharded collection, in this example, the people.json from MongoDB University: mongos> db.version(); 3.4.4 mongos> db.people.find().itcount(); 50474 mongos> db.people.find().skip(1000).itcount(); 49474 mongos> var stats = db.people.find().explain("executionStats"); mongos> print("nReturned: " + stats.executionStats.nReturned);var total=0;stats.executionStats.executionStages.shards.forEach(function(s){print(s.shardName+" nReturned: "+s.executionStages.nReturned); total += s.executionStages.nReturned;}); print("Sum of shards: " + total); nReturned: 50474 shard0000 nReturned: 12130 shard0001 nReturned: 13125 shard0002 nReturned: 25219 Sum of shards: 50474 mongos> var stats = db.people.find().skip(1000).explain("executionStats"); mongos> print("nReturned: " + stats.executionStats.nReturned);var total=0;stats.executionStats.executionStages.shards.forEach(function(s){print(s.shardName+" nReturned: "+s.executionStages.nReturned); total += s.executionStages.nReturned;}); print("Sum of shards: " + total); nReturned: 47474 shard0000 nReturned: 11130 shard0001 nReturned: 12125 shard0002 nReturned: 24219 Sum of shards: 47474
    • Query 2018-05-21

      This can lead to various problems such as:

      1. the following query does not include a SORT_KEY_GENERATOR stage: 
        db.sharded.explain().find().sort({a: 1});

        This is because the explain path on mongos diverges from the regular, non-explain path pretty early. In particular, the non-explain path will transform the query sent to the shards in transformQueryForShards. This transformation does not happen in the explain path, and causes us to leave off the projection {$sortKey: 1} which will signal to the shards that we intend to merge the sorted output on mongos.

      2. When using .explain("executionStats") on a query that includes a skip phase on sharded collection, the skip phase is applied separately at each queried shard. The result is each individual shard's nReturned is reduced by the skip_count, so the overall nReturned is reduced by (skip_count * shard_count).
      3. An "executionStats" explain with a limit is subject to a similar bug, as described in SERVER-32555.

            Assignee:
            backlog-query-optimization [DO NOT USE] Backlog - Query Optimization
            Reporter:
            joe.caswell@mongodb.com Joe Caswell
            Votes:
            0 Vote for this issue
            Watchers:
            19 Start watching this issue

              Created:
              Updated: