Palle Girgensohn <[EMAIL PROTECTED]> writes: > Vydefinition: SELECT p.userid, p.giver, p.first_name, p.last_name, p.email, > p.default_language, p.created, p.created_by, w.course_id FROM (person p > LEFT JOIN wiol w ON ((p.userid = w.userid)));
> explain analyze select p.pim_id, p.recipient, p.sender, p.message, p.ts, > p.type, case when sender.userid is not null then sender.first_name || ' ' > || sender.last_name else null end as sender_name, sender.course_id is not > null as is_online from pim p left outer join person_wiol_view sender on > (sender.userid = p.sender) where p.recipient = 'axto6551' and p.type >= 0 > limit 1; > explain analyze select p.pim_id, p.recipient, p.sender, p.message, p.ts, > p.type, case when sender.userid is not null then sender.first_name || ' ' > || sender.last_name else null end as sender_name, w.course_id is not null > as is_online from pim p left outer join person sender on (sender.userid = > p.sender) left join wiol w on (w.userid=sender.userid) where p.recipient = > 'axto6551' and p.type >= 0 limit 1; These are not actually the same query. In the former case the implicit parenthesization of the joins is pim left join (person left join wiol) whereas in the latter case the implicit parenthesization is left-to-right: (pim left join person) left join wiol Since the only restriction conditions you have provided are on pim, the first parenthesization implies forming the entire join of person and wiol :-(. If you were using plain joins then the two queries would be logically equivalent, but outer joins are in general not associative, so the planner will not consider re-ordering them. There is some work in 7.4 to make the planner smarter about outer joins, but offhand I don't think any of it will improve results for this particular example. I have seen some academic papers about how to prove that a particular pair of outer join operators can safely be swapped (as I think is true in this example). Some knowledge of that sort may eventually get into the planner, but it ain't there now. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings