X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id B2E8FD1B502 for ; Thu, 25 Sep 2003 13:56:05 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 00732-09 for ; Thu, 25 Sep 2003 10:55:37 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 8B5B9D1B4E1 for ; Thu, 25 Sep 2003 10:55:33 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h8PDtQ6c002588; Thu, 25 Sep 2003 09:55:26 -0400 (EDT) To: Palle Girgensohn Cc: pgsql-performance@postgresql.org Subject: Re: performance hit when joining with a view? In-reply-to: <31150000.1064493413@rambutan.pingpong.net> References: <31150000.1064493413@rambutan.pingpong.net> Comments: In-reply-to Palle Girgensohn message dated "Thu, 25 Sep 2003 14:36:53 +0200" Date: Thu, 25 Sep 2003 09:55:26 -0400 Message-ID: <2587.1064498126@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/319 X-Sequence-Number: 3685 Palle Girgensohn 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