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.
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.