title: Slow MongoDB query
body: <p>I am new to MongoDB and trying to understand why are my queries so slow (30-150 seconds per query!). My database contains about 60 million documents. In my queries, I need to combine parametric and fulltext search. This is the query I am trying to analyze:</p>
  <pre><code>db.collection.explain(&quot;executionStats&quot;).find(
      {&quot;property.multi.value_title&quot;: &quot;Pearson Education (US)&quot;, 
       &quot;pricing.price&quot; : {$gte: 70, $lte: 600}, 
        $text: { $search: &quot;app&quot; }
      }).count()
  </code></pre>
  <p>And this is the explain result:</p>
  <pre><code>{
          &quot;queryPlanner&quot; : {
                  &quot;plannerVersion&quot; : 1,
                  &quot;namespace&quot; : &quot;test.collection&quot;,
                  &quot;indexFilterSet&quot; : false,
                  &quot;parsedQuery&quot; : {
                          &quot;$and&quot; : [
                                  {
                                          &quot;property.multi.value_title&quot; : {
                                                  &quot;$eq&quot; : &quot;Pearson Education (US)&quot;
                                          }
                                  },
                                  {
                                          &quot;pricing.price&quot; : {
                                                  &quot;$lte&quot; : 600
                                          }
                                  },
                                  {
                                          &quot;pricing.price&quot; : {
                                                  &quot;$gte&quot; : 70
                                          }
                                  },
                                  {
                                          &quot;$text&quot; : {
                                                  &quot;$search&quot; : &quot;app&quot;,
                                                  &quot;$language&quot; : &quot;none&quot;,
                                                  &quot;$caseSensitive&quot; : false,
                                                  &quot;$diacriticSensitive&quot; : false
                                          }
                                  }
                          ]
                  },
                  &quot;winningPlan&quot; : {
                          &quot;stage&quot; : &quot;COUNT&quot;,
                          &quot;inputStage&quot; : {
                                  &quot;stage&quot; : &quot;FETCH&quot;,
                                  &quot;filter&quot; : {
                                          &quot;$and&quot; : [
                                                  {
                                                          &quot;pricing.price&quot; : {
                                                                  &quot;$lte&quot; : 600
                                                          }
                                                  },
                                                  {
                                                          &quot;pricing.price&quot; : {
                                                                  &quot;$gte&quot; : 70
                                                          }
                                                  },
                                                  {
                                                          &quot;property.multi.value_title&quot; : {
                                                                  &quot;$eq&quot; : &quot;Pearson Education (US)&quot;
                                                          }
                                                  }
                                          ]
                                  },
                                  &quot;inputStage&quot; : {
                                          &quot;stage&quot; : &quot;TEXT&quot;,
                                          &quot;indexPrefix&quot; : {
  
                                          },
                                          &quot;indexName&quot; : &quot;translate.cs.content_text&quot;,
                                          &quot;parsedTextQuery&quot; : {
                                                  &quot;terms&quot; : [
                                                          &quot;app&quot;
                                                  ],
                                                  &quot;negatedTerms&quot; : [ ],
                                                  &quot;phrases&quot; : [ ],
                                                  &quot;negatedPhrases&quot; : [ ]
                                          },
                                          &quot;textIndexVersion&quot; : 3,
                                          &quot;inputStage&quot; : {
                                                  &quot;stage&quot; : &quot;TEXT_MATCH&quot;,
                                                  &quot;inputStage&quot; : {
                                                          &quot;stage&quot; : &quot;FETCH&quot;,
                                                          &quot;inputStage&quot; : {
                                                                  &quot;stage&quot; : &quot;OR&quot;,
                                                                  &quot;inputStage&quot; : {
                                                                          &quot;stage&quot; : &quot;IXSCAN&quot;,
                                                                          &quot;keyPattern&quot; : {
                                                                                  &quot;_fts&quot; : &quot;text&quot;,
                                                                                  &quot;_ftsx&quot; : 1
                                                                          },
                                                                          &quot;indexName&quot; : &quot;translate.cs.content_text&quot;,
                                                                          &quot;isMultiKey&quot; : true,
                                                                          &quot;isUnique&quot; : false,
                                                                          &quot;isSparse&quot; : false,
                                                                          &quot;isPartial&quot; : false,
                                                                          &quot;indexVersion&quot; : 2,
                                                                          &quot;direction&quot; : &quot;backward&quot;,
                                                                          &quot;indexBounds&quot; : {
  
                                                                          }
                                                                  }
                                                          }
                                                  }
                                          }
                                  }
                          }
                  },
                  &quot;rejectedPlans&quot; : [ ]
          },
          &quot;executionStats&quot; : {
                  &quot;executionSuccess&quot; : true,
                  &quot;nReturned&quot; : 0,
                  &quot;executionTimeMillis&quot; : 29794,
                  &quot;totalKeysExamined&quot; : 7996,
                  &quot;totalDocsExamined&quot; : 15992,
                  &quot;executionStages&quot; : {
                          &quot;stage&quot; : &quot;COUNT&quot;,
                          &quot;nReturned&quot; : 0,
                          &quot;executionTimeMillisEstimate&quot; : 27400,
                          &quot;works&quot; : 7997,
                          &quot;advanced&quot; : 0,
                          &quot;needTime&quot; : 7996,
                          &quot;needYield&quot; : 0,
                          &quot;saveState&quot; : 1220,
                          &quot;restoreState&quot; : 1220,
                          &quot;isEOF&quot; : 1,
                          &quot;nCounted&quot; : 6,
                          &quot;nSkipped&quot; : 0,
                          &quot;inputStage&quot; : {
                                  &quot;stage&quot; : &quot;FETCH&quot;,
                                  &quot;filter&quot; : {
                                          &quot;$and&quot; : [
                                                  {
                                                          &quot;pricing.price&quot; : {
                                                                  &quot;$lte&quot; : 600
                                                          }
                                                  },
                                                  {
                                                          &quot;pricing.price&quot; : {
                                                                  &quot;$gte&quot; : 70
                                                          }
                                                  },
                                                  {
                                                          &quot;property.multi.value_title&quot; : {
                                                                  &quot;$eq&quot; : &quot;Pearson Education (US)&quot;
                                                          }
                                                  }
                                          ]
                                  },
                                  &quot;nReturned&quot; : 6,
                                  &quot;executionTimeMillisEstimate&quot; : 27400,
                                  &quot;works&quot; : 7997,
                                  &quot;advanced&quot; : 6,
                                  &quot;needTime&quot; : 7990,
                                  &quot;needYield&quot; : 0,
                                  &quot;saveState&quot; : 1220,
                                  &quot;restoreState&quot; : 1220,
                                  &quot;isEOF&quot; : 1,
                                  &quot;docsExamined&quot; : 7996,
                                  &quot;alreadyHasObj&quot; : 7996,
                                  &quot;inputStage&quot; : {
                                          &quot;stage&quot; : &quot;TEXT&quot;,
                                          &quot;nReturned&quot; : 7996,
                                          &quot;executionTimeMillisEstimate&quot; : 27390,
                                          &quot;works&quot; : 7997,
                                          &quot;advanced&quot; : 7996,
                                          &quot;needTime&quot; : 0,
                                          &quot;needYield&quot; : 0,
                                          &quot;saveState&quot; : 1220,
                                          &quot;restoreState&quot; : 1220,
                                          &quot;isEOF&quot; : 1,
                                          &quot;indexPrefix&quot; : {
  
                                          },
                                          &quot;indexName&quot; : &quot;translate.cs.content_text&quot;,
                                          &quot;parsedTextQuery&quot; : {
                                                  &quot;terms&quot; : [
                                                          &quot;app&quot;
                                                  ],
                                                  &quot;negatedTerms&quot; : [ ],
                                                  &quot;phrases&quot; : [ ],
                                                  &quot;negatedPhrases&quot; : [ ]
                                          },
                                          &quot;textIndexVersion&quot; : 3,
                                          &quot;inputStage&quot; : {
                                                  &quot;stage&quot; : &quot;TEXT_MATCH&quot;,
                                                  &quot;nReturned&quot; : 7996,
                                                  &quot;executionTimeMillisEstimate&quot; : 27390,
                                                  &quot;works&quot; : 7997,
                                                  &quot;advanced&quot; : 7996,
                                                  &quot;needTime&quot; : 0,
                                                  &quot;needYield&quot; : 0,
                                                  &quot;saveState&quot; : 1220,
                                                  &quot;restoreState&quot; : 1220,
                                                  &quot;isEOF&quot; : 1,
                                                  &quot;docsRejected&quot; : 0,
                                                  &quot;inputStage&quot; : {
                                                          &quot;stage&quot; : &quot;FETCH&quot;,
                                                          &quot;nReturned&quot; : 7996,
                                                          //HERE
                                                          &quot;executionTimeMillisEstimate&quot; : 27380,
                                                          &quot;works&quot; : 7997,
                                                          &quot;advanced&quot; : 7996,
                                                          &quot;needTime&quot; : 0,
                                                          &quot;needYield&quot; : 0,
                                                          &quot;saveState&quot; : 1220,
                                                          &quot;restoreState&quot; : 1220,
                                                          &quot;isEOF&quot; : 1,
                                                          &quot;docsExamined&quot; : 7996,
                                                          &quot;alreadyHasObj&quot; : 0,
                                                          &quot;inputStage&quot; : {
                                                                  &quot;stage&quot; : &quot;OR&quot;,
                                                                  &quot;nReturned&quot; : 7996,
                                                                  &quot;executionTimeMillisEstimate&quot; : 175,
                                                                  &quot;works&quot; : 7997,
                                                                  &quot;advanced&quot; : 7996,
                                                                  &quot;needTime&quot; : 0,
                                                                  &quot;needYield&quot; : 0,
                                                                  &quot;saveState&quot; : 1220,
                                                                  &quot;restoreState&quot; : 1220,
                                                                  &quot;isEOF&quot; : 1,
                                                                  &quot;dupsTested&quot; : 7996,
                                                                  &quot;dupsDropped&quot; : 0,
                                                                  &quot;inputStage&quot; : {
                                                                          &quot;stage&quot; : &quot;IXSCAN&quot;,
                                                                          &quot;nReturned&quot; : 7996,
                                                                          &quot;executionTimeMillisEstimate&quot; : 165,
                                                                          &quot;works&quot; : 7997,
                                                                          &quot;advanced&quot; : 7996,
                                                                          &quot;needTime&quot; : 0,
                                                                          &quot;needYield&quot; : 0,
                                                                          &quot;saveState&quot; : 1220,
                                                                          &quot;restoreState&quot; : 1220,
                                                                          &quot;isEOF&quot; : 1,
                                                                          &quot;keyPattern&quot; : {
                                                                                  &quot;_fts&quot; : &quot;text&quot;,
                                                                                  &quot;_ftsx&quot; : 1
                                                                          },
                                                                          &quot;indexName&quot; : &quot;translate.cs.content_text&quot;,
                                                                          &quot;isMultiKey&quot; : true,
                                                                          &quot;isUnique&quot; : false,
                                                                          &quot;isSparse&quot; : false,
                                                                          &quot;isPartial&quot; : false,
                                                                          &quot;indexVersion&quot; : 2,
                                                                          &quot;direction&quot; : &quot;backward&quot;,
                                                                          &quot;indexBounds&quot; : {
  
                                                                          },
                                                                          &quot;keysExamined&quot; : 7996,
                                                                          &quot;seeks&quot; : 1,
                                                                          &quot;dupsTested&quot; : 7996,
                                                                          &quot;dupsDropped&quot; : 0
                                                                  }
                                                          }
                                                  }
                                          }
                                  }
                          }
                  }
          },
          &quot;serverInfo&quot; : {
                  &quot;host&quot; : &quot;ubuntu2004&quot;,
                  &quot;port&quot; : 27017,
                  &quot;version&quot; : &quot;4.4.1&quot;,
                  &quot;gitVersion&quot; : &quot;ad91a93a5a31e175f5cbf8c69561e788bbc55ce1&quot;
          },
          &quot;ok&quot; : 1
  }
  </code></pre>
  <p>I couldn't specify my language in the text index, because it is not supported my MongoDB.</p>
  <p>I was looking at the time estimates at the executionStats part, and I noticed that after the <code>IXSCAN</code> and <code>OR</code> operations, that took about 170ms each, the time suddenly jumped to <code>&quot;executionTimeMillisEstimate&quot; : 27380</code> at the <code>FETCH</code> part.</p>
  <p>Can someone please explain what does it mean and how can I improve the performance of this query?</p>
  <p><strong>EDIT:</strong> these are indexes on my collection:</p>
  <pre><code>        {
              &quot;v&quot; : 2,
              &quot;key&quot; : {
                      &quot;_id&quot; : 1
              },
              &quot;name&quot; : &quot;_id_&quot;
      },
      {
              &quot;v&quot; : 2,
              &quot;key&quot; : {
                      &quot;_fts&quot; : &quot;text&quot;,
                      &quot;_ftsx&quot; : 1
              },
              &quot;name&quot; : &quot;translate.cs.content_text&quot;,
              &quot;default_language&quot; : &quot;none&quot;,
              &quot;language_override&quot; : &quot;none&quot;,
              &quot;weights&quot; : {
                      &quot;translate.cs.content&quot; : 1
              },
              &quot;textIndexVersion&quot; : 3
      },
      {
              &quot;v&quot; : 2,
              &quot;key&quot; : {
                      &quot;property&quot; : 1
              },
              &quot;name&quot; : &quot;property_1&quot;
      },
      {
              &quot;v&quot; : 2,
              &quot;key&quot; : {
                      &quot;property.multi.value_title&quot; : 1
              },
              &quot;name&quot; : &quot;property.multi.value_title_1&quot;
      }
  </code></pre>