Counting multiple fields of a document in each other document against different fields in the same collection

Question

I have millions of documents like this :

{
  "anId" : "xxxxx-yyyyy-zzzzzz"
  "field1": "value1",
  "field2" : [
       {"field1": "value2", ...}
    ]
}

I first $match equality on anId. Then my goal is to count on field1 and field2.field1 for each document in any other document so that:

field1 -> field1
field1 -> field2.field1
field2.field1 -> field1

And finally I want the result being like:

{"field1" : "value1", countings : 10}
{"field1" : "value2", countings : 5}

I'm trying with lookups but they are tremendously slow, even though foreignField's are indexed:

lookup1:

{
  from: 'same_collection',
  localField: 'field1',
  foreignField: 'field1',
  as: 'field1_field1'
}

lookup2:

{
  from: 'same_collection',
  localField: 'field1',
  foreignField: 'field2.field1',
  as: 'field1_field2'
}

lookup3:

{
  from: 'same_collection',
  localField: 'field2.field1',
  foreignField: 'field1',
  as: 'field2_field1'
}

Since I'm getting 3 lists, I'm concatenating them into one: project1:

{
  field1 : 1,
  concats : 
    {$concatArrays : 
      [{$ifNull: ['$field1_field1', []]},
       {$ifNull: ['$field1_field2', []]},
       {$ifNull: ['$field2_field1', []]}]
    }
}

Then I filter inside them, to ensure that each document takes concats which are not counted from the same looked up document (_id != $id), plus other filterings: project2:

{
  field1:1,
  concats: {
            $filter: {
               input: "$concats",
               as: "item",
               cond: { $and :[
                 {$eq: ["$$item.user_id", "23" ]},
                 {$ne: ["$$item._id","$_id"]},
                 {$eq: [{$type:'$$item.deleted'},"missing"]}
                 ]
               }
            }
         }
}

and then projecting finally: project3:

{
  _id : 0,
  field1:1,
  countings : {$size : "$concats"}
}

So, the collection has 24M docs. I'm limiting the query to 50K docs, because it times out otherwise. This means that, even though the lookups have 50K localFields to lookup from, the foreignField's to lookup to are anyway 23M (am I right?). I also tried with specialized lookups with let and pipeline, but they're even slower.

Execution stats (50K):

lookup1:
nReturned: 50000,
       executionTimeMillisEstimate: 5481 }

lookup2:
 nReturned: 50000,
       executionTimeMillisEstimate: 10245 }

lookup3:
nReturned: 50000,
       executionTimeMillisEstimate: 16911 }

project1:
 nReturned: 50000,
       executionTimeMillisEstimate: 16911 }

project2:
 nReturned: 50000,
       executionTimeMillisEstimate: 16911 }

project3:
nReturned: 50000,
       executionTimeMillisEstimate: 16913 } ]

Total:
 nReturned: 50000,
             executionTimeMillis: 16931,
             totalKeysExamined: 50000,
             totalDocsExamined: 50000

Execution stats (100k):

lookup1:
nReturned: 100000,
       executionTimeMillisEstimate: 16688 }

lookup2:
nReturned: 100000,
       executionTimeMillisEstimate: 27461 }

lookup3:
nReturned: 100000,
       executionTimeMillisEstimate: 35955 }

project1:
nReturned: 100000,
       executionTimeMillisEstimate: 35955 }

project2:
 nReturned: 100000,
       executionTimeMillisEstimate: 35963 }

project3:
nReturned: 100000,
       executionTimeMillisEstimate: 35965 } ]

total:
nReturned: 100000,
             executionTimeMillis: 35990,
             totalKeysExamined: 100000,
             totalDocsExamined: 100000

Is there any other way to achieve this faster?

user_10770370 · Answer

At the end of the day you're performing 3 lookups for 50k documents, those are 150k queries ( each performing an index scan). This means there's not much you can do to improve performance, especially considering hard limitations like x amount of readers per db.

The one thing that could potentially improve the run time is that these 3 $lookup could be ran in parallel instead of one after the other as there is some overhead in running them one after the other, this could be done using $facet:

{
    $facet: {
        field1_field1: [
            {
                $lookup: {
                    from: 'same_collection',
                    localField: 'field1',
                    foreignField: 'field1',
                    as: 'field1_field1',
                },
            },
        ],
        field1_field2: [
            {
                $lookup: {
                    from: 'same_collection',
                    localField: 'field1',
                    foreignField: 'field2.field1',
                    as: 'field1_field2',
                },
            },
        ],
        field2_field1: [
            {
                $lookup: {
                    from: 'same_collection',
                    localField: 'field2.field1',
                    foreignField: 'field1',
                    as: 'field2_field1',
                },

            },
        ],
    },
}

Now this is still an incredible work load burst for the db to handle so this might not improve performance by a lot, it depends how loaded your server is, but it does give the potential to scale up. Also worth noting that you'll need to change your $concatArrays slightly as the structure will be different.

I will also mention other factors to consider that only you can know, for example if any of your stages exceed the 100mb memory limit and you use the allowDiskUse flag, this means Mongo is writing and reading results to/from the disk. If that's the case I would consider moving the "lookups" to queries in code ( under the presumption that the network between the two servers is fast as this is also a consideration for such move ). There are quite a few hardware limitations to think about, I believe this is where you could actually get a meaningful performance boost.

Counting multiple fields of a document in each other document against different fields in the same collection

1 Answers