[SERVER-30130] Covered query with empty find() without specifying hint Created: 13/Jul/17  Updated: 24/Aug/17  Resolved: 13/Jul/17

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

Type: Bug Priority: Minor - P4
Reporter: Owen Allen Assignee: Mark Agarunov
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Issue Links:
Duplicate
duplicates SERVER-20066 Query planner should consider index s... Closed
Operating System: ALL
Steps To Reproduce:

db.test.ensureIndex({ x : 1, y : 1 });
for(var i = 0; i < 100; i++) { db.test.insert({ a : i, x : i, y : i }); }
db.test.find({}, { x : 1, y : 1, _id : 0 }).explain(true)
db.test.find({}, { x : 1, y : 1, _id : 0 }).hint("x_1_y_1").explain(true)

Participants:

 Description   

From my tests MongoDB does not cover queries well when the query is empty. A simple example of this is as follows:

db.test.ensureIndex({ x : 1, y : 1 });
for(var i = 0; i < 100; i++) { db.test.insert({ a : i, x : i, y : i }); }
 
db.test.find({}, { x : 1, y : 1, _id : 0 }).explain(true)

In my eyes the above code should be a covered query. According to my tests it does not use the index at all, the stages returned are "PROJECTION" -> "COLLSCAN". If I pass a hint, it does return a covered query.

db.test.find({}, { x : 1, y : 1, _id : 0 }).hint("x_1_y_1").explain(true);

That returns "PROJECTION" -> "IXSCAN" and the totalDocsExamined is 0.

If I pass have a predicate for the query (

{ x : 10 }

) such that it selects that index, it returns covered. If I attempt to do a sort on the index, it still does not consistently find it. Some of my tests it does, some of them it does not.

To me it feels possible to perform an optimization where if the query is empty, and the requested fields directly match to an index, it should be able to perform a covered query without the need to manually specify hint().



 Comments   
Comment by Mark Agarunov [ 13/Jul/17 ]

Hello, owenallenaz,

Thank you for the report. Looking over the example you've provided, this appears to be the same issue that was reported in SERVER-20066 so I've closed this ticket as a duplicate. Please watch SERVER-20066 for any updates on this.

Also note that a covered index scan is not likely to be faster than a collection scan unless the documents in the collection are very large. For example, after increasing the size of the dataset used in your example, the times are comparable between the index scan and the collection scan:

2017-07-13T15:49:55.305-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:393472142276 keysExamined:101 docsExamined:0 numYields:0 nreturned:101 reslen:3092 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:55.321-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 393472142276, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:393472142276 keysExamined:10006 docsExamined:0 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 15ms
2017-07-13T15:49:55.948-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:391365595558 keysExamined:101 docsExamined:0 numYields:0 nreturned:101 reslen:3092 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:55.964-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 391365595558, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:391365595558 keysExamined:10006 docsExamined:0 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 15ms
2017-07-13T15:49:56.532-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:392367017885 keysExamined:101 docsExamined:0 numYields:0 nreturned:101 reslen:3092 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:56.551-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 392367017885, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, hint: "x_1_y_1", $db: "test" } planSummary: IXSCAN { x: 1, y: 1 } cursorid:392367017885 keysExamined:10006 docsExamined:0 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 18ms
2017-07-13T15:49:58.389-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:393162986699 keysExamined:0 docsExamined:101 numYields:0 nreturned:101 reslen:3050 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:58.407-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 393162986699, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:393162986699 keysExamined:0 docsExamined:10006 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 17ms
2017-07-13T15:49:58.973-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:391738741774 keysExamined:0 docsExamined:101 numYields:0 nreturned:101 reslen:3050 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:58.987-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 391738741774, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:391738741774 keysExamined:0 docsExamined:10006 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 12ms
2017-07-13T15:49:59.524-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:393031273255 keysExamined:0 docsExamined:101 numYields:0 nreturned:101 reslen:3050 locks:{ Global: { acquireCount: { r: 2 } }, Database: { acquireCount: { r: 1 } }, Collection: { acquireCount: { r: 1 } } } protocol:op_msg 0ms
2017-07-13T15:49:59.537-0400 I COMMAND  [conn25] command test.test appName: "MongoDB Shell" command: getMore { getMore: 393031273255, collection: "test", $db: "test" } originatingCommand: { find: "test", filter: {}, projection: { x: 1.0, y: 1.0, _id: 0.0 }, $db: "test" } planSummary: COLLSCAN cursorid:393031273255 keysExamined:0 docsExamined:10006 cursorExhausted:1 numYields:78 nreturned:10006 reslen:329175 locks:{ Global: { acquireCount: { r: 158 } }, Database: { acquireCount: { r: 79 } }, Collection: { acquireCount: { r: 79 } } } protocol:op_msg 12ms

Thanks,
Mark

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