1. The Command We Will Follow

The SELECT of MongoDB is find(). In this post we follow one command from start to finish, using the students collection:

db.students.find({ age: { $gt: 20 } })
  .sort({ age: -1 })
  .limit(2)

SELECT * FROM students
WHERE age > 20
ORDER BY age DESC
FETCH FIRST 2 ROWS ONLY;

In words: give me the students older than 20, sorted by age from highest to lowest, and return only the first 2.

2. What Happens Inside When You Run find()

This is the path of the command from the client to the result. The notes on the right show what can stop the command or what happens at that step.

+----------------------------------------------+
| 1. CLIENT (mongosh / driver)                 |
|    sends the find command to mongod          |
+-----------------------+----------------------+
                        |  BSON over TCP
                        v
+----------------------------------------------+
| 2. AUTHORIZATION CHECK                       |   NO  --> Unauthorized error (stop)
|    does the user have the find privilege?    |
+-----------------------+----------------------+
                        | YES
                        v
+----------------------------------------------+
| 3. PARSE THE QUERY                           |   BAD --> syntax error (stop)
|    check the filter, build the query shape   |   e.g. unknown operator $greaterthan
+-----------------------+----------------------+
                        | OK
                        v
+----------------------------------------------+
| 4. LOCK AND SNAPSHOT                         |   intent-shared lock: does not block
|    WiredTiger opens a consistent snapshot    |   other readers or writers
+-----------------------+----------------------+
                        |
                        v
+----------------------------------------------+
| 5. QUERY PLANNER  (see Section 3)            |   plan cache hit  --> reuse the plan
|    choose how to find the documents          |   plan cache miss --> trial run
+-----------------------+----------------------+
                        |
                        v
+----------------------------------------------+
| 6. PLAN EXECUTOR                             |   gives up its locks from time to time
|    runs the stages and reads the data        |   so other operations can proceed
+-----------------------+----------------------+
                        |
                        v
+----------------------------------------------+
| 7. WIREDTIGER reads the pages                |   in cache?     use it
|    index and collection are B-trees          |   not in cache? read disk, decompress,
|                                              |   add to cache
+-----------------------+----------------------+
                        |
                        v
+----------------------------------------------+
| 8. FIRST BATCH sent to the client            |   up to 101 documents or 16 MB
|    cursor id is kept if more results remain  |   more? the client sends getMore
+----------------------------------------------+

Good to know:

• find() returns a cursor. The command is sent to the server only when the shell starts reading the cursor.
• mongosh prints 20 documents at a time. Type it to get the next ones (this sends getMore).
• An idle cursor is closed after 10 minutes.
• An in-memory sort is limited to 100 MB. From MongoDB 6.0, a bigger sort spills to disk by default.
• An operation slower than slowms (100 ms by default) is written to the log.
• Sharded cluster: the client talks to mongos. If the filter has the shard key, mongos sends the query only to the needed shards. If not, it asks all shards (scatter-gather) and merges the results.

The matching Oracle terms:

3. How MongoDB Chooses and Runs a Plan

This is step 5 in detail. Oracle’s optimizer picks a plan using stored statistics. MongoDB does not use stored column statistics. It tries the possible plans for a short time and keeps the winner.

Plan cache has this query shape?
        |
   YES  +-----------------------+  NO
   |                            |
   v                            v
Use the cached plan      List the candidate plans:
(no trial needed)        COLLSCAN and each usable index
   |                            |
   |                            v
   |                     Trial run: the plans take turns
   |                     until one is clearly ahead
   |                            |
   |                            v
   |                     The winner is saved in the plan cache
   |                            |
   +------------+---------------+
                |
                v
        Execute the chosen plan

The query shape is the query without its values, so age > 20 and age > 25 share one plan cache entry. The plan cache is cleared when an index is created or dropped, when the collection is dropped, and when the server restarts. If a cached plan later performs badly, MongoDB drops it and runs a new trial.

The chosen plan is a tree of stages. Read each tree from the bottom (first stage) to the top (last stage). Here are three cases for our command:

SORT (age: -1, limit 2)
  |
  +-- COLLSCAN (age > 20)

LIMIT (2)
  |
  +-- FETCH
        |
        +-- IXSCAN (age_1, backward,
                    age > 20)

PROJECTION_COVERED
  |
  +-- IXSCAN (age_1, age > 20)

Case C needs a projection that returns only indexed fields and removes _id, because _id is returned by default and is not in the age_1 index:

db.students.find(
  { age: { $gt: 20 } },
  { age: 1, _id: 0 }
)

Key point: A good index changes the plan in two ways. It avoids reading the whole collection, and it can remove the SORT stage completely. To force an index, use hint({ age: 1 }), which is like the Oracle /*+ INDEX(students idx_age) */ hint.

4. See It Yourself

Run explain() to see the real plan. The stage tree is in winningPlan.

// Create the index
db.students.createIndex({ age: 1 })

// Run the query with explain
db.students.find({ age: { $gt: 20 } })
  .sort({ age: -1 })
  .limit(2)
  .explain("executionStats")

// Also show the plans that lost the trial run
db.students.find({ age: { $gt: 20 } })
  .explain("allPlansExecution")

// View the plan cache
db.students.aggregate([ { $planCacheStats: {} } ])

The important fields in the output:

Bottom line for Oracle DBAs: explain("executionStats") is your DBMS_XPLAN.DISPLAY_CURSOR. Compare totalDocsExamined with nReturned. When they are far apart, the query is doing extra work, just like a full table scan that returns few rows.