'AVG' and 'SUM' functionality in MongoDB, any tips?

Give map-reduce a try, it's probably not as slow as you think. I've used it for real-time aggregation over some large data sets, and although it's sometimes not lightning fast, it's more often fine. It's best if you can filter down the size of the initial data you're aggregating, e.g.:

db.collection.mapReduce(m, r, { query : { year: 2011 } });

If you need to speed things up even more, consider distributing the data over a sharded cluster. Then the map-reduce processing can be scaled out across multiple shards running in parallel.

MongoDB notes

OK, so Map/Reduce and aggregation have some serious issues currently.

Big caveat: the MongoDB instance can only have one "javascript engine" instance. This means that you cannot run two simultaneous Map/Reduces on the server. And you only get one core for running the map-reduce.

In the case of what you are doing, you're basically "rolling your own" M/R. The downside is the extra network traffic. The upside is that you can now throw more cores at the problem (from the web-servers).

Your key question

I can't precalculate a lot of my sums/averages because the selection of values to sum/average is almost always different

There is no general method for optimizing "all possible" queries. If you want the system to be able to sum and aggregate along every field for every range, then you will eventually find a set of fields/ranges that are too big.

The way to "solve" this is to reduce the set of fields and ranges.

So keep daily / hourly counters and sum over those counters. At the least you reduce the number of documents you need to scan in order to answer your query.

Simple answer is:

1. If it possible precalculate everything you can precalculate.
2. If you need aggregate data by date ranges and aggregation should work as quick as possible then use map/reduce + sharding to distribute calculation across multiple machines.

But in same time mongodb guide say:

The price of using MapReduce is speed: group is not particularly speedy, but MapReduce is slower and is not supposed to be used in “real time.” You run MapReduce as a background job, it creates a collection of results, and then you can query that collection in real time.

So it sounds like mongodb is not best solution for real time data aggregation.