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

Performance Regression on 4.4 compared to 4.2 on Aggregate query

    • ALL
    • Query 2020-12-28, Query 2021-01-11, Query 2021-01-25, Query 2021-02-08

      Hi, We recently updated our systems to mongo 4.4, we face a several regression on the performance for the aggregation on the new version.

       

      This query: 

       

      mongo --eval 'db.getCollection("country_esp_static").aggregate([{$match:{$and:[
      {"shape":"geoHash"}
      ,{$or:[{"tags.level0":{$in:["esp"]}}]}]}},{$match:{$and:[\{"data.UN_2010_E":{$gte:1}},\\{"data.UN_2010_E":{$lte:37917}}]}},{$addFields:{"weightFilters":[{$multiply:[{$cond:[{$gte:["$data.UN_2010_E",374.9025723525453]},{$add:[{$multiply:[{$divide:[{$subtract:["$data.UN_2010_E",374.9025723525453]},7508.41948552949]},10]},50]},{$multiply:[{$divide:[{$subtract:["$data.UN_2010_E",1]},74.78051447050906]},10]}]},1]}]}},\{$addFields:{"weight":{$sum:"$weightFilters"}}},{$match:{$and:[\{"weight":{$gte:0}},\\{"weight":{$lte:100}}]}},\{$group:{"_id":"","UN_2010_E":{$sum:"$data.UN_2010_E"},"A18_24B":\{$sum:"$data.A18_24B"},"A25_34B":\{$sum:"$data.A25_34B"},"A35_44B":\{$sum:"$data.A35_44B"},"A45_54B":\{$sum:"$data.A45_54B"},"A55_64B":\{$sum:"$data.A55_64B"},"A65PLUSB":\{$sum:"$data.A65PLUSB"},"F_2010_E":\{$sum:"$data.F_2010_E"},"M_2010_E":\{$sum:"$data.M_2010_E"},"B_AFRICA":\{$sum:"$data.B_AFRICA"},"B_ASIA":\{$sum:"$data.B_ASIA"},"B_CS_AMERICA":\{$sum:"$data.B_CS_AMERICA"},"B_NOT_UE":\{$sum:"$data.B_NOT_UE"},"B_N_AMERICA":\{$sum:"$data.B_N_AMERICA"},"B_OCEANIA":\{$sum:"$data.B_OCEANIA"},"B_SPAIN":\{$sum:"$data.B_SPAIN"},"B_UE":\{$sum:"$data.B_UE"},"NOT_SPANISH":\{$sum:"$data.NOT_SPANISH"},"SPANISH":\{$sum:"$data.SPANISH"},"MS_DIVORCED":\{$sum:"$data.MS_DIVORCED"},"MS_MARRIED":\{$sum:"$data.MS_MARRIED"},"MS_SEPARATED":\{$sum:"$data.MS_SEPARATED"},"MS_SINGLE":\{$sum:"$data.MS_SINGLE"},"MS_WIDOW":\{$sum:"$data.MS_WIDOW"},"SL_ILLITERATE":\{$sum:"$data.SL_ILLITERATE"},"SL_LEVEL1":\{$sum:"$data.SL_LEVEL1"},"SL_LEVEL2":\{$sum:"$data.SL_LEVEL2"},"SL_LEVEL3":\{$sum:"$data.SL_LEVEL3"},"SL_NOTINFO":\{$sum:"$data.SL_NOTINFO"},"SL_UNEDUCATED":\{$sum:"$data.SL_UNEDUCATED"},"IAB1":\{$sum:"$data.IAB1"},"IAB10":\{$sum:"$data.IAB10"},"IAB11":\{$sum:"$data.IAB11"},"IAB12":\{$sum:"$data.IAB12"},"IAB13":\{$sum:"$data.IAB13"},"IAB14":\{$sum:"$data.IAB14"},"IAB15":\{$sum:"$data.IAB15"},"IAB16":\{$sum:"$data.IAB16"},"IAB17":\{$sum:"$data.IAB17"},"IAB18":\{$sum:"$data.IAB18"},"IAB19":\{$sum:"$data.IAB19"},"IAB2":\{$sum:"$data.IAB2"},"IAB20":\{$sum:"$data.IAB20"},"IAB21":\{$sum:"$data.IAB21"},"IAB22":\{$sum:"$data.IAB22"},"IAB23":\{$sum:"$data.IAB23"},"IAB3":\{$sum:"$data.IAB3"},"IAB4":\{$sum:"$data.IAB4"},"IAB5":\{$sum:"$data.IAB5"},"IAB6":\{$sum:"$data.IAB6"},"IAB7":\{$sum:"$data.IAB7"},"IAB8":\{$sum:"$data.IAB8"},"IAB9":\{$sum:"$data.IAB9"},"HOME_1PERSONS":\{$sum:"$data.HOME_1PERSONS"},"HOME_2PERSONS":\{$sum:"$data.HOME_2PERSONS"},"HOME_3PERSONS":\{$sum:"$data.HOME_3PERSONS"},"HOME_4PERSONS":\{$sum:"$data.HOME_4PERSONS"},"HOME_5PERSONS":\{$sum:"$data.HOME_5PERSONS"},"HOME_6PERSONS":\{$sum:"$data.HOME_6PERSONS"},"HOME_TOTAL":\{$sum:"$data.HOME_TOTAL"},"TEN_106_120M":\{$sum:"$data.TEN_106_120M"},"TEN_121_150M":\{$sum:"$data.TEN_121_150M"},"TEN_151_180M":\{$sum:"$data.TEN_151_180M"},"TEN_1ROOM":\{$sum:"$data.TEN_1ROOM"},"TEN_2ROOM":\{$sum:"$data.TEN_2ROOM"},"TEN_30_45M":\{$sum:"$data.TEN_30_45M"},"TEN_3ROOM":\{$sum:"$data.TEN_3ROOM"},"TEN_46_60M":\{$sum:"$data.TEN_46_60M"},"TEN_4ROOM":\{$sum:"$data.TEN_4ROOM"},"TEN_5ROOM":\{$sum:"$data.TEN_5ROOM"},"TEN_61_75M":\{$sum:"$data.TEN_61_75M"},"TEN_6ROOM":\{$sum:"$data.TEN_6ROOM"},"TEN_76_90M":\{$sum:"$data.TEN_76_90M"},"TEN_7ROOM":\{$sum:"$data.TEN_7ROOM"},"TEN_8ROOM":\{$sum:"$data.TEN_8ROOM"},"TEN_91_105M":\{$sum:"$data.TEN_91_105M"},"TEN_EMPTY":\{$sum:"$data.TEN_EMPTY"},"TEN_LESS30M":\{$sum:"$data.TEN_LESS30M"},"TEN_MAIN":\{$sum:"$data.TEN_MAIN"},"TEN_MORE180M":\{$sum:"$data.TEN_MORE180M"},"TEN_MORE8ROOM":\{$sum:"$data.TEN_MORE8ROOM"},"TEN_OWNED":\{$sum:"$data.TEN_OWNED"},"TEN_OWNED_INHERITED":\{$sum:"$data.TEN_OWNED_INHERITED"},"TEN_OWNED_PAYING":\{$sum:"$data.TEN_OWNED_PAYING"},"TEN_REND_LOW_COST":\{$sum:"$data.TEN_REND_LOW_COST"},"TEN_REND_OTHER":\{$sum:"$data.TEN_REND_OTHER"},"TEN_RENT":\{$sum:"$data.TEN_RENT"},"TEN_SECOND":\{$sum:"$data.TEN_SECOND"},"TOTAL_TEN":\{$sum:"$data.TOTAL_TEN"},"digitalAffinity_general_conversion":\{$avg:"$data.digitalAffinity_general_conversion"},"digitalAffinity_general_ctr":\{$avg:"$data.digitalAffinity_general_ctr"},"digitalAffinity_general_digitalConversion":\{$avg:"$data.digitalAffinity_general_digitalConversion"},"digitalAffinity_general_digitalRate":\{$avg:"$data.digitalAffinity_general_digitalRate"},"digitalAffinity_general_displayEngagement":\{$avg:"$data.digitalAffinity_general_displayEngagement"},"digitalAffinity_general_displaySaturation":\{$avg:"$data.digitalAffinity_general_displaySaturation"},"digitalAffinity_general_engagement":\{$avg:"$data.digitalAffinity_general_engagement"},"digitalAffinity_general_saturation":\{$avg:"$data.digitalAffinity_general_saturation"},"digitalAffinity_general_storeVisitConversion":\{$avg:"$data.digitalAffinity_general_storeVisitConversion"},"digitalAffinity_general_videoEngagement":\{$avg:"$data.digitalAffinity_general_videoEngagement"},"digitalAffinity_general_videoSaturation":\{$avg:"$data.digitalAffinity_general_videoSaturation"},"digitalAffinity_general_vtr":\{$avg:"$data.digitalAffinity_general_vtr"},"digitalAffinity_automotive_conversion":\{$avg:"$data.digitalAffinity_automotive_conversion"},"digitalAffinity_automotive_ctr":\{$avg:"$data.digitalAffinity_automotive_ctr"},"digitalAffinity_automotive_digitalConversion":\{$avg:"$data.digitalAffinity_automotive_digitalConversion"},"digitalAffinity_automotive_digitalRate":\{$avg:"$data.digitalAffinity_automotive_digitalRate"},"digitalAffinity_automotive_displayEngagement":\{$avg:"$data.digitalAffinity_automotive_displayEngagement"},"digitalAffinity_automotive_displaySaturation":\{$avg:"$data.digitalAffinity_automotive_displaySaturation"},"digitalAffinity_automotive_engagement":\{$avg:"$data.digitalAffinity_automotive_engagement"},"digitalAffinity_automotive_saturation":\{$avg:"$data.digitalAffinity_automotive_saturation"},"digitalAffinity_automotive_storeVisitConversion":\{$avg:"$data.digitalAffinity_automotive_storeVisitConversion"},"digitalAffinity_automotive_videoEngagement":\{$avg:"$data.digitalAffinity_automotive_videoEngagement"},"digitalAffinity_automotive_videoSaturation":\{$avg:"$data.digitalAffinity_automotive_videoSaturation"},"digitalAffinity_automotive_vtr":\{$avg:"$data.digitalAffinity_automotive_vtr"},"digitalAffinity_consumerPackageGoods_conversion":\{$avg:"$data.digitalAffinity_consumerPackageGoods_conversion"},"digitalAffinity_consumerPackageGoods_ctr":\{$avg:"$data.digitalAffinity_consumerPackageGoods_ctr"},"digitalAffinity_consumerPackageGoods_digitalConversion":\{$avg:"$data.digitalAffinity_consumerPackageGoods_digitalConversion"},"digitalAffinity_consumerPackageGoods_digitalRate":\{$avg:"$data.digitalAffinity_consumerPackageGoods_digitalRate"},"digitalAffinity_consumerPackageGoods_displayEngagement":\{$avg:"$data.digitalAffinity_consumerPackageGoods_displayEngagement"},"digitalAffinity_consumerPackageGoods_displaySaturation":\{$avg:"$data.digitalAffinity_consumerPackageGoods_displaySaturation"},"digitalAffinity_consumerPackageGoods_engagement":\{$avg:"$data.digitalAffinity_consumerPackageGoods_engagement"},"digitalAffinity_consumerPackageGoods_saturation":\{$avg:"$data.digitalAffinity_consumerPackageGoods_saturation"},"digitalAffinity_consumerPackageGoods_storeVisitConversion":\{$avg:"$data.digitalAffinity_consumerPackageGoods_storeVisitConversion"},"digitalAffinity_consumerPackageGoods_videoEngagement":\{$avg:"$data.digitalAffinity_consumerPackageGoods_videoEngagement"},"digitalAffinity_consumerPackageGoods_videoSaturation":\{$avg:"$data.digitalAffinity_consumerPackageGoods_videoSaturation"},"digitalAffinity_consumerPackageGoods_vtr":\{$avg:"$data.digitalAffinity_consumerPackageGoods_vtr"},"digitalAffinity_fashion_conversion":\{$avg:"$data.digitalAffinity_fashion_conversion"},"digitalAffinity_fashion_ctr":\{$avg:"$data.digitalAffinity_fashion_ctr"},"digitalAffinity_fashion_digitalConversion":\{$avg:"$data.digitalAffinity_fashion_digitalConversion"},"digitalAffinity_fashion_digitalRate":\{$avg:"$data.digitalAffinity_fashion_digitalRate"},"digitalAffinity_fashion_displayEngagement":\{$avg:"$data.digitalAffinity_fashion_displayEngagement"},"digitalAffinity_fashion_displaySaturation":\{$avg:"$data.digitalAffinity_fashion_displaySaturation"},"digitalAffinity_fashion_engagement":\{$avg:"$data.digitalAffinity_fashion_engagement"},"digitalAffinity_fashion_saturation":\{$avg:"$data.digitalAffinity_fashion_saturation"},"digitalAffinity_fashion_storeVisitConversion":\{$avg:"$data.digitalAffinity_fashion_storeVisitConversion"},"digitalAffinity_fashion_videoEngagement":\{$avg:"$data.digitalAffinity_fashion_videoEngagement"},"digitalAffinity_fashion_videoSaturation":\{$avg:"$data.digitalAffinity_fashion_videoSaturation"},"digitalAffinity_fashion_vtr":\{$avg:"$data.digitalAffinity_fashion_vtr"},"digitalAffinity_retail_conversion":\{$avg:"$data.digitalAffinity_retail_conversion"},"digitalAffinity_retail_ctr":\{$avg:"$data.digitalAffinity_retail_ctr"},"digitalAffinity_retail_digitalConversion":\{$avg:"$data.digitalAffinity_retail_digitalConversion"},"digitalAffinity_retail_digitalRate":\{$avg:"$data.digitalAffinity_retail_digitalRate"},"digitalAffinity_retail_displayEngagement":\{$avg:"$data.digitalAffinity_retail_displayEngagement"},"digitalAffinity_retail_displaySaturation":\{$avg:"$data.digitalAffinity_retail_displaySaturation"},"digitalAffinity_retail_engagement":\{$avg:"$data.digitalAffinity_retail_engagement"},"digitalAffinity_retail_saturation":\{$avg:"$data.digitalAffinity_retail_saturation"},"digitalAffinity_retail_storeVisitConversion":\{$avg:"$data.digitalAffinity_retail_storeVisitConversion"},"digitalAffinity_retail_videoEngagement":\{$avg:"$data.digitalAffinity_retail_videoEngagement"},"digitalAffinity_retail_videoSaturation":\{$avg:"$data.digitalAffinity_retail_videoSaturation"},"digitalAffinity_retail_vtr":\{$avg:"$data.digitalAffinity_retail_vtr"},"digitalAffinity_techServices_conversion":\{$avg:"$data.digitalAffinity_techServices_conversion"}}}])' sonataDemographyDevelop
      

       

       

      takes around 5 secs on 4.0 and 4.2, and more than 20 seconds on 4.4, attached are the explain for both systems:

       

       

       

       

        1. 4_2_diagnostic.data.tar-2.gz
          694 kB
          _torgus@gmail.com
        2. 4_2 stats.png
          250 kB
          Edwin Zhou
        3. 4_4_diagnostic.data.tar-2.gz
          103 kB
          _torgus@gmail.com
        4. 4_4 stats.png
          240 kB
          Edwin Zhou
        5. explain_4.4
          24 kB
          _torgus@gmail.com
        6. explain4.0
          37 kB
          _torgus@gmail.com

            Assignee:
            charlie.swanson@mongodb.com Charlie Swanson
            Reporter:
            torgus@gmail.com JuanMa D
            Votes:
            0 Vote for this issue
            Watchers:
            12 Start watching this issue

              Created:
              Updated:
              Resolved: