BSON Parsing very slow in nested array

[C3po-D2rd2]

Hello,

We are benchmarking MongoDB against PSQL in order to migrate our 40T data base to Mongo. Unfortunatly we are not able to reach the same level of performances and it seems that it is due to BSON parsing of the answer from MongoDB.

We used an extract of DB to benchmark, so only 734M of documents, each record are looking like that:

{ id: string, group_id: string, value: decimal, pressures: [{type: string, value: decimal}], impacys: [{type: string, value: decimal}] }

As you can see we have two array of object nested in our record.
In our Benchmark let's say we are retrieving data by group_id. each group_id should have between 3K to 5K records.

here is the performance results:

	User CPU time	DB time	Total time	Real time elapsed	Context
MongoDB count	3,82 ms	0,8 ms	4,62ms	16,18 ms	Counting
PostgreSQL	1,88 ms	0,17 ms	2,04 ms	14,17 ms
PostgreSQL get query	95,1 ms	12,5 ms	107,6 ms	250,9 ms	Retrieving all content
MongoDB : mongoid	424,4 ms	11,4 ms	435 ms	772 ms
MongoDB : MongoBSON	271,7 ms	6,78 ms	278,5 ms	478,9 ms
PSQL get	38,4 ms	1,25 ms	39,7 ms	72 ms	Only id and quantity
MongoDB get	17,5 ms	0,53 ms	18,1 ms	38,9 ms
PSQL get	36,1 ms	3,37 ms	39,5 ms	68,4 ms	With pressures
MongoDB get	73,8 ms	1,65 ms	75,4 ms	129,3 ms
PSQL get	44,4 ms	7,8 ms	52,5 ms	90,4 ms	With pressures and impacts
MongoDB get	151,4 ms	4,48 ms	155,9 ms	234,1 ms
PSQL get	87,1 ms	3,6 ms	90,7 ms	133,5 ms	All but arrays
MongoDB get	60 ms	0,92 ms	60,9 ms	85,4 ms

Mongo is very efficient to retrieve the data (see DB time) but CPU is high. We try excluding array or only arrays and we realised that they were considerably slowing down the process. Using MongoBSON our self allowed us to divide by 2 the performance issue (excluding mongoid ORM from the solution) yet we are still far from the 250ms we have we psql (VS 478ms with MongoBSON).

Digging still, we understood that half of the time spent was in MongoBSON parsing which disappear if we only retrieve id and quantity.

we are trying to split the pressures and impacts in different "table" but I am very surprised of this issue. May be we are not doing something right. Can anyone help us on this matter?

Thank you