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

Filter order is random even when generated from the same SBE plan.

    • Type: Icon: Bug Bug
    • Resolution: Fixed
    • Priority: Icon: Major - P3 Major - P3
    • 7.3.0-rc0
    • Affects Version/s: None
    • Component/s: None
    • Query Execution
    • Fully Compatible
    • ALL
    • Hide

      I would recommend running the query with explain("executionStats") so it uses a (potentially) different filter every time instead of using the same filter over and over again from the plan cache.

      Show
      I would recommend running the query with explain("executionStats") so it uses a (potentially) different filter every time instead of using the same filter over and over again from the plan cache.

      While running some queries with explain("executionStats"), I noticed that there was a lot of variance on query duration but it seemed to fall into a handful of distinct buckets of query duration. Upon further investigation, even though the SBE plan was identical each run, the field order of the filters were random. This meant that the query was fast when the selective part of the filter came first, and slow when the selective part came last. I've attached the executionStats output of a slow and fast set of filters. The query was run against the dataset defined in this workload.

      [
        {
          $match:
            {
              $and:
                [
                  {
                    $or:
                      [
                        { "obj.measurement1": 4 },
                        { "obj.measurement1": 9 },
                      ],
                  },
                  { "obj.measurement2": { $lte: 175 } },
                  { "obj.measurement3": { $gt: 100, $lt: 800 } },
                ],
            },
        },
        { $count: "count" },
      ];
      

        1. plan_fast.txt
          18 kB
        2. plan_slow.txt
          18 kB

            Assignee:
            ian.boros@mongodb.com Ian Boros
            Reporter:
            parker.felix@mongodb.com Parker Felix
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated:
              Resolved: