1

I have collection with about 700,000 documents. These documents represent locations in a hex grid. There are only 500 different locations (ll_16k). The aggregation of the 700,000 documents into 500 documents takes about 2 seconds.

  { $group:
    { _id: "$properties.ll_16k",
      total:
        { $sum: "$properties.price" },
      count:
        { $sum: 1 }
    }
  }

The same action in Postgres is flying... This query performs routinely under 500ms.

SELECT
  ll_16k,
  count(ll_16k),
  sum(price)
FROM ppd_2015_perf
GROUP BY ll_16k;

I am just getting started with MongoDB. Am I doing something wrong here or is this the expected performance difference?

This is a test subset. The actual dataset will be 20,000,000 documents/records in size and expected to grow about 1,000,000 records per year.

3
  • I'm afraid you are comparing apples to pears here.
    – dezso
    Aug 11, 2016 at 15:26
  • I know that the two systems are entirely different and that different performances are expected. I just want to know from someone who knows MongoDB whether my aggregate query is best way to do this or whether I should perhaps use map.reduce. Hands are tied on this project and I need to use MongoDB so I am trying to make the best out of this. Aug 11, 2016 at 15:43
  • 1
    In this case, you should probably reword your question a bit, to keep the attention on the MongoDB side.
    – dezso
    Aug 11, 2016 at 15:45

1 Answer 1

2

One difference to consider is the amount of IO happening. For Postgres data is normalised into tables and tables are read one page at at time. To complete the SQL it need only read however much disk is required to store all of table ppd_2015_perf.

Mongo's unit of IO, however, is a whole document. All the denormalised/ duplicated stuff comes in along with the three fields in which you're interested.

Of course there a lot of things assumed here - RAM, compression, disk performance and data models being some.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.