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

Investigate performance regression in parsing a long constant array as input to $filter

XMLWordPrintableJSON

    • Type: Icon: Bug Bug
    • Resolution: Done
    • Priority: Icon: Major - P3 Major - P3
    • None
    • Affects Version/s: 7.0.0
    • Component/s: None
    • Query Optimization
    • Fully Compatible
    • ALL
    • Hide 
      for (i=10; i<200000; i=i*10) {    arr=[];    for (j=0; j<i; j++) {       arr.push({ k: "ABC"+j, v: "ZYX"+j});    }    print("Length of array is " + arr.length);    sp = { "$project" : { "_id" : true, "imsi" : "$_id.imsi", "vpmn" : { "$filter" : { "input" : arr, "as" : "item", "cond" : { "$eq" : [ "$$item.k", "$_id.vpmn" ] } } } } };    t0=new Date(); db.foo.aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.explain().aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.explain().aggregate(sp); t1=new Date(); print(t1-t0); }
      Show
      for (i=10; i<200000; i=i*10) {    arr=[];    for (j=0; j<i; j++) {       arr.push({ k: "ABC"+j, v: "ZYX"+j});    }    print("Length of array is " + arr.length);    sp = { "$project" : { "_id" : true, "imsi" : "$_id.imsi", "vpmn" : { "$filter" : { "input" : arr, "as" : "item", "cond" : { "$eq" : [ "$$item.k", "$_id.vpmn" ] } } } } };    t0=new Date(); db.foo.aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.explain().aggregate(sp); t1=new Date(); print(t1-t0);    t0=new Date(); db.foo.explain().aggregate(sp); t1=new Date(); print(t1-t0); }
    • QO 2024-07-22

      It appears that somewhere between 6.0.15 and 7.0 the parsing of a large constant array given as input to $filter got about 2x to 4x slower... 

      Using the script below I get:

      Array Length   Execution  6 Explain 6 Execution 7 Explain 7
      10 1 0 1 1
      100 1 1 6 5
      1000 7 5 12 10
      10000 63 49 110 105
      100000 695 530 1200 1110

      so it seems to stabilize at 2x which is not great but it's pretty horrible at specific example size I originally tried (like 4x).   Also 7.0.0 was bad but 7.0.11 seemed to be worse.  

        1. post_query_stats_backport_sleep.svg
          821 kB
        2. pre_query_stats_backport_sleep.svg
          565 kB

            Assignee:
            lynne.wang@mongodb.com Lynne Wang
            Reporter:
            asya.kamsky@mongodb.com Asya Kamsky
            Votes:
            0 Vote for this issue
            Watchers:
            9 Start watching this issue

              Created:
              Updated:
              Resolved: