MotionQL

Explain plan analyzer

Paste the output of explain() to see the plan as a tree, catch collection scans and in-memory sorts, compare documents examined with documents returned, and get an index suggestion.

Runs entirely in your browser. Nothing you paste is sent to any server.

Tip: run .explain("executionStats") for real counts. Find, aggregate, count, distinct, sharded and SBE plans are supported.

Findings

Warning: In-memory (blocking) sort

Results are sorted after they are fetched on {"createdAt":-1}. An index whose keys match the sort lets MongoDB return documents already in order, and avoids the 100 MB sort memory limit.

Warning: Examined 5,000 documents to return 400

That is 13x more than returned. A more selective index would read fewer documents.

Suggested index

db.orders.createIndex({ status: 1, createdAt: -1, qty: 1 })

Replaces the collection scan. Order: equality fields, then sort, then range (ESR). Check it against your other queries and existing indexes before creating it; every index slows writes a little.

Plan for shop.orders

Returned

400

Docs examined

5,000

13 per result

Keys examined

0

Time

6 ms

Stages run from the innermost (bottom) up to the top.

  • SORTsort {"createdAt":-1}

    400 returned · ~0 ms

    • COLLSCAN

      400 returned · 5,000 docs examined · ~0 ms

      filter {"$and":[{"status":{"$eq":"A"}},{"qty":{"$lt":30}}]}

Do this against your real data with MotionQL

MotionQL has an Explain tab on every query and flags COLLSCAN and in-memory sorts with a badge before you run the query. Pro is free for a year.

Download MotionQLSee features

Reading an explain plan

A plan is a tree of stages. The innermost stage reads data (COLLSCAN or IXSCAN), and each stage above it filters, fetches, sorts or projects. Three numbers tell most of the story:

  • nReturned: documents the query returned.
  • totalKeysExamined: index entries read. Ideally close to nReturned.
  • totalDocsExamined: documents loaded. Zero for a covered query; far above nReturned means the index is not selective enough, or there is none.

Good plans read about as many keys and documents as they return, and have no COLLSCAN or SORT stage. When there is a sort, put equality fields first in the index, then the sort fields, then range fields.

FAQ

Questions, answered

Append .explain("executionStats") to a find, e.g. db.orders.find({ status: "A" }).sort({ createdAt: -1 }).explain("executionStats"), or use db.orders.explain("executionStats").aggregate([...]) for a pipeline. Copy the whole result. mongosh output with Long(...) and other shell types is fine.

More free MongoDB tools

Connection string builder

Build or parse mongodb:// and mongodb+srv:// URIs. Split a pasted URI into hosts, credentials and options, catch common mistakes, and copy a correctly encoded string with the password masked.

Open tool

ObjectId converter

Decode a MongoDB ObjectId into its creation time, random value and counter, decode many at once, or generate ObjectIds for any date to query by time range.

Open tool

Extended JSON converter

Convert MongoDB documents between canonical Extended JSON, relaxed Extended JSON and mongosh shell syntax, keeping BSON types like ObjectId, Date, Long and Decimal128 intact.

Open tool

BSON size calculator

Paste a document to get its exact BSON size in bytes, see how close it is to MongoDB's 16 MB document limit, and find the fields taking up the most space.

Open tool

Operators cheat sheet

Searchable reference of MongoDB query, update and aggregation operators and pipeline stages, each with example syntax and a link to the official documentation.

Open tool