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

Sandro Dentella wrote:
> 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
>>

My thinking was that by putting the conditions in the WHERE clause you
were restricting the rows returned by the entire query, while putting
them in the ON clause only restricts the matching in the JOIN itself.

>
> 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?
>

I really need to read up more on that myself. Looking at the above I
really couldn't tell you which is the 'better' query plan.

>