Uploaded image for project: 'Core Server'
  1. Core Server
  2. SERVER-82217

Filter query with sort performs much worse with SBE

    • Type: Icon: Bug Bug
    • Resolution: Duplicate
    • Priority: Icon: Minor - P4 Minor - P4
    • None
    • Affects Version/s: 7.0.2
    • Component/s: None
    • None
    • Query Execution
    • ALL
    • QE 2023-11-27, QE 2023-12-11

      We have recently upgraded from 6.0.4 to Mongo 7.0.2 and noticed a significant drop in query performance for certain filter queries using a sort.

      For example, this query takes 13 seconds with SBE:

       

      rs0 [direct: secondary] mydb> db.version();
      7.0.2
      rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).sort({ _id: -1 }).explain('executionStats').executionStats.executionTimeMillis;
      13246 

       

      With the classic engine it takes 1.5 seconds:

      rs0 [direct: secondary] mydb> db.adminCommand({ setParameter: 1, internalQueryFrameworkControl: 'forceClassicEngine'})
      {
        was: 'forceClassicEngine',
        ok: 1,
        ..
      }
      rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).sort({ _id: -1 }).explain('executionStats').executionStats.executionTimeMillis;
      1554 

      The collection contains around 600K documents and there is a compound index that contains the "fsType" field (as the first key).

      It looks like with SBE the query doesn't use the index because it seems to be processing all 600k documents. I've attached the explain outputs for the queries as files.

      Without the sort param SBE performs well (and seems to use the index):

       

      rs0 [direct: secondary] mydb> db.mycoll.find({"fsType": "PageRef","page.formData.pt_title.value": "a"}).explain('executionStats').executionStats.executionTimeMillis;
      276

       

        1. query-sbe-explain.txt
          9 kB
        2. query-classic-explain.txt
          5 kB
        3. query-sbe-explain-wihout-sort.txt
          7 kB
        4. repro.js
          0.9 kB

            Assignee:
            ivan.fefer@mongodb.com Ivan Fefer
            Reporter:
            philipp.boersteken@crownpeak.com Philipp Boe
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: