Databases like PostgreSQL are excellent at performing joins -- which is all this subselect really is, namely joining two relations -- even when the datasets are quite large.
But this particular MongoDB query comparison is pretty worthless, since it's simply giving an example of denormalization, a concept which is equally applicable to relational databases -- the main difference being that with MongoDB, you hardly have a choice in the matter, since joins don't exist.
Don't get me wrong, I love MongoDB, but there are much better reasons to use MongoDB, such as the fact that every document is a flexible data structure, not a strict collection of columns. You can add keys and values as you choose, and store them as arrays or sub-documents depending on the encapsulation you need, etc.
So generally you will have an easier time working with data and being impulsive about it, than the square-hole-fitting-only-square-pegs model of relational databases, which require more planning and schema design, which in turn tends to squeeze all the fun out of working with databases.
There are pros and cons to both approaches, of course. MongoDB is not as mature as modern relational databases, by far. On the other hand, it has a nice feature which nobody apparently mentions: With MongoDB, the old relational theorist's pet peeve about the meaning of null values becomes moot, because in MongoDB a null value (ie., a missing value) is simply a value which is not there, ie. its key is simply not there. That's much better than null values!
Another advantage is the ability to work with hetereogenous collections of data without having to jump through too many hoops. For example, you can have a collection (table) called "publications". In this table you can store different kinds of publications: Books, magazines, comics, newspapers and so on. Each type of publication may have some common fields, but many have type-specific fields -- hence, hetereogenous data.
A relational database designer will tell you that in the relational world, you would denormalize. A central "publications" table with all the common columns, and then tables "books", "magazines", etc., with each table having their type-specific columns, and also having a foreign-key reference back to the "publications" table. Fine. But think of all the joins you will need just in order to list all and query this stuff; if you have only the publication ID, you have to go through all the tables to determine what type of publication it is. There's not just the performance aspect. The relational model is quite different to how people _think_ about data. MongoDB is easier on the brain, that way.