"Erik Aronesty" <[EMAIL PROTECTED]> writes: > Should I start looking to figure out why the optimizer didn't figure out > that it should be doing this sort of thing?
It looks to me that the problem is that convert_IN_to_join() is not being smart about where to attach the IN's subselect to the join tree. It's just adding it to the top FROM-expression, so that the join tree is effectively ((sites left join quota) IN-join usersites) and since we don't currently allow any rearrangement of outer joins, this cannot be rearranged into ((sites IN-join usersites) left join quota) as you'd like. The really clean solution to this would be to implement logic about when it is safe to rearrange the join order of outer joins. But I think that's a fairly hard problem in general. A shorter-term solution might be possible by teaching convert_IN_to_join() to attach the IN subselect further down in the join tree, using logic similar to what we use to decide where ordinary WHERE quals can bubble down to. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match