pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Palle Girgensohn <girgen@pingpong.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance hit when joining with a view?
Date: Thu, 25 Sep 2003 09:55:26 -0400
Message-ID: <2587.1064498126@sss.pgh.pa.us> (raw)
In-Reply-To: <31150000.1064493413@rambutan.pingpong.net>
References: <31150000.1064493413@rambutan.pingpong.net>

Palle Girgensohn <girgen@pingpong.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



view thread (2+ messages)

Message-ID: <2587.1064498126@sss.pgh.pa.us>
Permalink:  ../2587.1064498126@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/2587.1064498126@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, girgen@pingpong.net
  Subject: Re: performance hit when joining with a view?
  In-Reply-To: <2587.1064498126@sss.pgh.pa.us>
  References: <31150000.1064493413@rambutan.pingpong.net> <2587.1064498126@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox