I've also run into O(n) and even O(n*m) # queries situations. They're entirely avoidable by simply sticking closer to the relational model than the object model. Sadly, this is almost always impossible with just Django's ORM. You very quickly end up writing unmanaged models and raw SQL.
SQLAlchemy is so much better when it comes to this, since things like joins are always explicit. However, it's rather hard to use it together with the Django ORM bits, especially the admin. One method I want to explore is replacing only the raw SQL bits with SQLAlchemy, and use its reflection to map to the Django ORM models.
It's also important to remember that the Django ORM's select_related call only goes one way. Consider the case:
class Clan(models.Model):
...
class Player(models.Model):
clan = models.ForeignKey(Clan)
Doing Clan.objects.all().select_related() will preload all the Player objects associated with the Clans. Doing Player.objects.all().select_related() will _not_ select the Clan objects, and should you refer to them in any way, will result in a query per clan you access.
In my case, where I ordered a list of players by their clans, in pages with ~200 players, I was doing ~200 queries. Django provides a workaround for this (the {% regroup x by y %} template tag), and now supports doing joins in Python in 1.4 (I forget the call, however) so that the ORM can cope with this case... but this behaviour caught me off guard, and is something to keep in mind.
You're absolutely correct in that hand-writing the SQL would fix this issue too, but then, as you say, you lose database portability.
Personally, after years of trying to solve the Object/Relational mismatch, I realized that it's better to drop the object side and stay with the relational side if you need a database.
The price is a little bit of syntactic sugar. The return is the ability to reason about your database and how it accesses data.
web2py eschewed ORM for a DAL (Database Abstraction Layer). I do not know how it compares to SQLAlchemy, but it is independent of everything else web2py; I've been using it in non-web projects and it is excellent, although a little slow if you have large result sets.
SQLAlchemy is philosophically similar to DAL from what I'm reading, but closer to pure set algebra than SQL. On top of that, it builds SQL-specific features and declarative models (as objects).
Agree with the "stick to relational model advice". In my team we stopped writing classes for each tables, using simple modules and functions instead and the result is less code, less state, more explicit parameter passing, less circular dependencies, less classmethods, less properties, more atomic tests, and more decoupled code.
SQLAlchemy is so much better when it comes to this, since things like joins are always explicit. However, it's rather hard to use it together with the Django ORM bits, especially the admin. One method I want to explore is replacing only the raw SQL bits with SQLAlchemy, and use its reflection to map to the Django ORM models.