Quote:
Originally Posted by Bobblehead
Something looks strange there - how many tables do you have?
I see cat, animal, house and animal_house. You are only using animal_house in the inner query to determine the cat_id, but when you move out one level you don't have any relation between house and cat or animal. Animal and cat are an inner join, but with no relation to house you are triggering a cartesian join. When you maually propagate the house_id inside the inner query it is probably still a cartesian, but since you have limited the result set it is really fast, and the optimizer isn't intelligent enough to propagate the value first.
I don't use postgres, but based on your description that is what it looks like to me.
|
Yeah, you are right. But I'd be surprised if the cartesian join was causing the performance hit since there is a direct filter on house.id restricting house to a single row. If the same query format were used to find # of cats in, say, 10 houses, then you will get slower performance (though not noticeably), but also invalid results. Bottom line - it's a very poor query structure and even the fact that it is auto-generated isn't much of an excuse. I'd expect even an auto-generated query could come up with something more like:
Code:
select count(cat) from cat, animal, house_animal, house where
cat.id = animal.id and
animal.id = house_animal.animal_id and
house_animal.house_id = house.id and
house.id = 10690