I have to analyze this data annually, monthly and daily (for example, how much we earned in each month of the year).
I have to use $ group here.
But it is very slow and takes up to 15 seconds.
my aggregation :
A compound index that starts with amount is surely useless for most use case. It is for this one for sure. You $match on date.year and date.month. That is the index you need. Your date should be a date not an object of 2 strings. See https://docs.mongodb.com/manual/reference/bson-types/#date
What is the total size (in MB) of your collection?
What is the RAM in your machine?
What kind of disks?
To sum all amounts an index on amount is sufficient. You might have to $project amount first. To sum the amount by date an compound index date,amount may also be helped with a $project after $match.
collection size is 2.7Gb.
machine ram → 32Gb ddr4 ecc 2700
hard drive → ssd nvme 512Gb.
The only problem I have with mongodb is that if this problem is solved it is the best database I have worked with.
Thank you
I see nothing wrong with the specs. Hope these are the specs of the server on which you run mongod. Do you run anything else on this? Can you share your mongod configuration file? I want to make sure you do not play with some parameters that restrict the storage engine.
Have you replaced your date object with a single native date field?
I do not think anybody can answer that. What I can say is that it is my preferred. So much, that as a independent old and grumpy veteran contractor, I now refuse contract that involve SQL because I have more fun with MongoDB. It helps me solve a bigger variety of problems with less planning, coding and maintenance work.
I see nothing that would stop the storage engine to use your RAM correctly.
One thing to remember is that if you sum your 12M documents with a cold system, the 12M documents needs to be read from disk which may impact performance. You should always evaluate performance on a warm running server where the working set is in RAM.