[SERVER-83057] Filter order is random even when generated from the same SBE plan. Created: 09/Nov/23  Updated: 30/Jan/24  Resolved: 11/Dec/23

Status: Closed
Project: Core Server
Component/s: None
Affects Version/s: None
Fix Version/s: 7.3.0-rc0

Type: Bug Priority: Major - P3
Reporter: Parker Felix Assignee: Ian Boros
Resolution: Fixed Votes: 0
Labels: quick-win
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified

Attachments: Text File plan_fast.txt     Text File plan_slow.txt    
Assigned Teams:
Query Execution
Backwards Compatibility: Fully Compatible
Operating System: ALL
Steps To Reproduce:

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.

Participants:

 Description   

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" },
];



 Comments   
Comment by Githook User [ 06/Dec/23 ]

Author:

{'name': 'Ian Boros', 'email': 'ian.boros@mongodb.com', 'username': 'borosaurus'}

Message: SERVER-83057 Simplify residual filter construction for TS in SBE

GitOrigin-RevId: e474e3ff1b3aac406b305ed36e6bae2f7cd424f8
Branch: master
https://github.com/mongodb/mongo/commit/855efbc2b0f7cc4444564fe423fbf070c4cb6e98

Comment by Chris Hutchinson [ 17/Nov/23 ]

Issue could reside in stage builder or query planner. Investigating the stage builder first, if nothing found we will pass on to QO.

Generated at Thu Feb 08 06:51:07 UTC 2024 using Jira 9.7.1#970001-sha1:2222b88b221c4928ef0de3161136cc90c8356a66.