Sort and Match Optimization

Working with Data
1 
{
    "_id" : ObjectId("62c3ff3114d4f445ef75d1b5"),
    "ver" : "1.0",
    "mac" : "123456789000243",
    "did" : "123456789000243",
    "dvt" : "water",
    "dvm" : "jvt1440",
    "tid" : "1ce9effd-b291-4f65-bbe6-ed06f1dca80c",
    "type" : "water_metering_data",
    "source" : "EV",
    "eventCode" : "332",
    "location" : {
        "_id" : ObjectId("62c3ff3114d4f445ef75d1b6"),
        "accuracy" : NumberInt(0),
        "timestamp" : ISODate("2022-07-05T08:00:13.000+0000")
    },
    "timezone" : "Asia/Calcutta",
    "meter" : {
        "ver" : NumberInt(1),
        "sgp" : NumberInt(28),
        "sgq" : NumberInt(30),
        "sgn" : NumberInt(-10),
        "tms" : ISODate("2022-07-05T08:00:13.000+0000"),
        "sno" : NumberInt(1472),
        "waterConsumption" : NumberInt(200),
        "waterBatteryVoltage" : 3.4,
        "waterBatteryStatus" : NumberInt(70),
        "tid" : NumberInt(263843937)
    },
    "time" : 1657008013000.0,
    "meterId" : "123456789000243",
    "watchId" : "621f1a71d46261f4385e94e9",
    "groupId" : null,
    "parentId" : "61558575921c023a93f81362",
    "device" : ObjectId("621f1a71d46261f4385e94e6"),
    "rootDat" : "61558575921c023a93f81362",
    "dat" : "61558575921c023a93f81362",
    "assetCode" : "water",
    "actionTaken" : false,
    "createdAt" : ISODate("2022-07-05T09:06:57.130+0000"),
    "updatedAt" : ISODate("2022-07-05T09:07:09.696+0000"),
    "__v" : NumberInt(0),
    "isWaterStat" : NumberInt(1),
    "migrated" : true,
    "isMeterStat" : 0.0,
    "tsFlag" : true
}

This is how a document looks like, now I need to calculate some value for which I am using aggregation pipeline and I am using the match and sort operators first, what I am using is.

 $match: {
        dat: { $regex: "^" + eventStat.dat },
        time: {
          $gte: eventStat.time.from,
          $lte: eventStat.time.to,
        },
      },

$sort: { time: 1 }

So I am using this two opeartors in the pipeline first,

Now Mongodb Document says that aggregation will always implement match first before sort but in some cases it performs sort first, I am not sure but I think that happens when there is a index on field key used in sort not present in match and Mongodb decides it better to sort first. Here I am using time in both match and sort so I want to know that is there still any case possible where sort might happen before match? If yes, I read that a dummy project operator can force it to match first but what exactly is a dummy project opeartor?

2

Hi @Harsh_Bhudolia ,

If you have an index like {time : 1, dat : 1} then it is better for the query to use the index to sort first and then run a range scan for all the events starting with the string.

This is according to the esr rule:

Now in your specific syntax I am not sure the dynamic regex will be able to pickup the range correctly so I would try and write the match:

 dat: /^eventStat.dat/

Have you looked at the explain plan of this query

Thanks
Pavel

3

How do I force mongoDb to apply match first?

6

There is no way to force it. If the index is used it might be better to doe a sort + match together on the index.

Can you provide an explain plan?

7

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.