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

MongoDB Time-Spatial Query is slower with 2dsphere Index

    XMLWordPrintable

Details

    • Improvement
    • Status: Closed
    • Major - P3
    • Resolution: Duplicate
    • 4.4.3
    • None
    • None
    • None

    Description

      Recently, I started to investigate the performance of MongoDB with AIS Data. I used a collection with 19m documents with proper field types as described in the definition. I also created a new geoloc field with type: (Point) from coordinates (lon,lat) in this same collection.

      The query under investigation is:

      db.nari_dynamic.explain("executionStats").aggregate(
        [
          {
            $match: {
              geoloc: {
                $geoWithin: {
                  $geometry: {
                    type: "Polygon",
                    coordinates: [
                      [
                        [-5.0, 45.0],
                        [+0.0, 45.0],
                        [+0.0, 50.0],
                        [-5.0, 50.0],
                        [-5.0, 45.0],
                      ],
                    ],
                  },
                },
              },
            },
          },
       
          {
            $group: {
              _id: "$sourcemmsi",
              PointCount: { $sum: 1 },
       
              MinDatePoint: { $min: { date: "$t3" } },
              MaxDatePoint: { $max: { date: "$t3" } },
            },
          },
          { $sort: { _id: 1 } },
          { $limit: 100 },
          { $project: { _id: 1, PointCount: 1, MinDatePoint: 1, MaxDatePoint: 1 } },
        ],
        { explain: true }
      );
      

      During investigation and testing I found the following:

      {{}}

      1. Without any index: 94s
      2. With geoloc-2dsphere index: 280s

      Of course, I understand that is more complex as the query has a grouping function, but the idea is that usually, we will get something quicker and not slower with the index unless the index causes a different sorting inside the engine like near does.

      {{}}

      Attachments

        1. QUERY
          41 kB
        2. QUERY2
          31 kB
        3. QUERY3
          37 kB

        Issue Links

          Activity

            People

              edwin.zhou@mongodb.com Edwin Zhou
              koureasstavros@gmail.com Stavros Koureas
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: