[SERVER-1812] Add the ability to get query results and the query plan in one call Created: 20/Sep/10  Updated: 06/Dec/22

Status: Backlog
Project: Core Server
Component/s: Performance, Querying
Affects Version/s: 1.6.1, 1.7.0
Fix Version/s: None

Type: Improvement Priority: Major - P3
Reporter: Justin Dearing Assignee: Backlog - Query Execution
Resolution: Unresolved Votes: 1
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

All


Issue Links:
Duplicate
is duplicated by SERVER-42104 add flag to query result document ind... Closed
is duplicated by SERVER-50428 Expose diagnostics to client queries Closed
Related
related to SERVER-10448 Revamp explain() formatting Closed
is related to SERVER-42104 add flag to query result document ind... Closed
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Participants:

 Description   

.explain() on a query returns the query plan instead of the query itself. It would be nice to be able to get the query results and the execution plan. This would be useful from the shell, as well as from the APIs. SQL Server Management Studio, the GUI that comes with MS SQL 2k5/2k8/2k8R2 has this functionality and its very useful when debugging. It allows you to tweak a query and ensure that the result set did not change in addition to measuring performance.



 Comments   
Comment by Craig Homa [ 31/Jul/19 ]

Re-opening as future plans to improve sharded explain will make this an easy and useful improvement to implement.

Comment by Asya Kamsky [ 04/Dec/17 ]

Closing since as of 3.4 significant amount of information about execution plan is available in the logs.

Comment by Asya Kamsky [ 04/Dec/17 ]

We now support this since the logs for operations show the number of index keys affected.
Example:

2017-12-04T14:12:23.343-0500 I COMMAND  [conn11] command test.lotsofindexes appName: "MongoDB Shell" command: insert { insert: "lotsofindexes", documents: [ { _id: ObjectId('5a259e1763f82b808d8e53e1'), a: 10.0, b: 20.0 } ], ordered: true, $db: "test" } ninserted:1 keysInserted:9 numYields:0 reslen:29 locks:{ Global: { acquireCount: { r: 1, w: 1 } }, Database: { acquireCount: { w: 1 } }, Collection: { acquireCount: { w: 1 } } } protocol:op_msg 12ms

This is a log line for single insert into a collection with nine indexes. Note keysInserted:9 field.

Comment by Justin Dearing [ 21/Sep/10 ]

insert().explain() Would serve several purposes:

  • When triggers get implemented, there would potentially be more than "insert row, write index1 write index2" to explain. One would assume the trigger results would be explained too.
  • If a user happens to have 50 indexes on a collection and wonder why my collection is slow, a user might run explain before looking at the number of indexes on my query. From a UX (or I guess Programmer/Admin eXperience) point of view, its a more intuitive way to figure out why inserts are slow.
  • From a UX point of view, the programmer/admin expects to be able to see planner output from all atomic CRUD operations.
  • It would be a good diagnostic tool if there ever was a bug whereby some inserts were not updating all indexes.
  • It would lead to the next logical step of allowing you to profile stored javascript, the way you can profile the execution plan of a stored proc in SQL server. You could be able to provide a breakdown of the execution of each query in the javascript. Ditto for passing a multi statement script passed to eval().
Comment by Eliot Horowitz (Inactive) [ 21/Sep/10 ]

explains don't really make sense on insert as you know its going to touch every index.
not really sure what you would have i to do

Comment by Justin Dearing [ 21/Sep/10 ]

Eliot,

I'll dig more deeply into what SSMS does.

Some things I just noticed now:

1. I can do var foo = db.foo.find() and call explain on that after getting results. So this is already possible.
2. I can't call explain on an insert. I'll try updates in the morning.

Let me do some more research/thinking about this before closing this ticket. Insert should definitely have an explain to list the indexes it adds, if its getting replicated, etc, etc. Then again how do you get that from an insert that isn't safe?

Comment by Eliot Horowitz (Inactive) [ 21/Sep/10 ]

Not sure what sql manager is doing - probably just doing the explain and the query separately.

Generated at Thu Feb 08 02:58:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.