Re: performance hit when joining with a view?

Palle Girgensohn <girgen(at)pingpong(dot)net> 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