Re: Simple OUTER JOIN doubt - Mailing list pgsql-general

On Fri, Oct 27, 2006 at 02:42:06PM -0500, Russ Brown wrote:
> Looks to me like it is because you're referencing vota_punteggio in the
> WHERE clause.
>
> Try something like this:
>
>        SELECT f.id, f.titolo, p.voto
>        FROM film_film f
>        LEFT OUTER JOIN vota_punteggio  p
>        ON f.id = p.film_id
>        AND p.user_id = 2
>

this works in fact, and it's simpler. But I don't really understard why I
should put it in this way.

On Fri, Oct 27, 2006 at 03:55:35PM -0400, Tom Lane wrote:
> [ scratches head... ]  Looks all right to me.  Are you sure you copied
> what you typed accurately?  Does EXPLAIN show that a left join is being
> used?

here is the explain for both queries:

cinemino=# explain       SELECT f.id, f.titolo, p.voto
       FROM film_film f
       LEFT OUTER JOIN vota_punteggio  p
       ON (f.id = p.film_id)
       WHERE
       (p.user_id = 2 OR p.user_id IS NULL)
;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Merge Right Join  (cost=18.00..106.26 rows=170 width=76)
   Merge Cond: ("outer".film_id = "inner".id)
   Filter: (("outer".user_id = 2) OR ("outer".user_id IS NULL))
   ->  Index Scan using vota_punteggio_film_id on vota_punteggio p  (cost=0.00..59.93 rows=1630 width=12)
   ->  Sort  (cost=18.00..18.42 rows=170 width=72)
         Sort Key: f.id
         ->  Seq Scan on film_film f  (cost=0.00..11.70 rows=170 width=72)
(7 righe)

cinemino=# explain       SELECT f.id, f.titolo, p.voto
       FROM film_film f
       LEFT OUTER JOIN vota_punteggio  p
       ON (f.id = p.film_id AND p.user_id = 2)
;
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Hash Left Join  (cost=12.05..24.67 rows=170 width=76)
   Hash Cond: ("outer".id = "inner".film_id)
   ->  Seq Scan on film_film f  (cost=0.00..11.70 rows=170 width=72)
   ->  Hash  (cost=12.03..12.03 rows=8 width=8)
         ->  Bitmap Heap Scan on vota_punteggio p  (cost=2.03..12.03 rows=8 width=8)
               Recheck Cond: (user_id = 2)
               ->  Bitmap Index Scan on vota_punteggio_user_id  (cost=0.00..2.03 rows=8 width=0)
                     Index Cond: (user_id = 2)
(8 righe)




BTW: I'm no able to read explain output, but it's a long time I want to
     start studying them. I think I should start studying chapter 13, other
     hints on this subject?


--
Sandro Dentella  *:-)
e-mail: sandro@e-den.it
http://www.tksql.org                    TkSQL Home page - My GPL work