Operational Defect Database

BugZero found this defect 2419 days ago.

MongoDB | 403738

[SERVER-30130] Covered query with empty find() without specifying hint

Last update date:

8/24/2017

Affected products:

MongoDB Server

Affected releases:

3.2.12

Fixed releases:

No fixed releases provided.

Description:

Info

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().

Top User Comments

mark.agarunov commented on Thu, 13 Jul 2017 20:10:09 +0000: 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

Additional Resources / Links

Share:

BugZero Risk Score

Coming soon

Status

Closed

Have you been affected by this bug?

cost-cta-background

Do you know how much operational outages are costing you?

Understand the cost to your business and how BugZero can help you reduce those costs.

Discussion

Login to read and write comments.

Have you ever...

had your data corrupted from a

VMware

bug?

Search:

...