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.
__________________
"The problem with any ideology is that it gives the answer before you look at the evidence."
—Bill Clinton
"The greatest obstacle to discovery is not ignorance--it is the illusion of knowledge."
—Daniel J. Boorstin, historian, former Librarian of Congress
"But the Senator, while insisting he was not intoxicated, could not explain his nudity"
—WKRP in Cincinatti
|