Re: indexing for left join

I am new to PostgreSQL but isn't this query the same as doing an INNER 
JOIN?

For a true LEFT JOIN should it not be as follows?

SELECT ITEM.ITEM_PK
FROM ITEM
LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
AND SERIAL_NO.NO ='WX1234'
GROUP BY ITEM.ITEM_PK

Using an AND instead of WHERE for the predicate on SERIAL_NO.NO results 
in very different plans despite the immature statistics. The following 
plan is for the true LEFT JOIN.
                               QUERY PLAN
---------------------------------------------------------------------------HashAggregate  (cost=2.10..2.13 rows=3
width=4) ->  Hash Left Join  (cost=1.04..2.10 rows=3 width=4)        Hash Cond: ("outer".item_pk = "inner".item_fk)
  ->  Seq Scan on item  (cost=0.00..1.03 rows=3 width=4)        ->  Hash  (cost=1.04..1.04 rows=1 width=4)
-> Seq Scan on serial_no  (cost=0.00..1.04 rows=1 width=4)                    Filter: (("no")::text = 'WX1234'::text)
 
(7 rows)

The next plan, which is very similary to your original plan, is for the 
INNER JOIN you described.                            QUERY PLAN
---------------------------------------------------------------------HashAggregate  (cost=2.11..2.12 rows=1 width=4)
-> Nested Loop  (cost=0.00..2.11 rows=1 width=4)        Join Filter: ("outer".item_fk = "inner".item_pk)        ->  Seq
Scanon serial_no  (cost=0.00..1.04 rows=1 width=4)              Filter: (("no")::text = 'WX1234'::text)        ->  Seq
Scanon item  (cost=0.00..1.03 rows=3 width=4)
 
(6 rows)

I wont speculate on how these plans would converge or diverge as the 
tables grew and the statistics matured.

- Zulq Alam

T E Schmitz wrote:
> SELECT ITEM.ITEM_PK FROM ITEM
> LEFT JOIN SERIAL_NO ON SERIAL_NO.ITEM_FK = ITEM.ITEM_PK
> WHERE SERIAL_NO.NO ='WX1234'
> GROUP BY ITEM.ITEM_PK

> I ran an EXPLAIN:
> HashAggregate  (cost=1.06..1.06 rows=1 width=4)
> ->  Nested Loop  (cost=0.00..1.06 rows=1 width=4)
>     Join Filter: ("inner".item_fk = "outer".item_pk)
>     ->  Seq Scan on item  (cost=0.00..0.00 rows=1 width=4)
>     ->  Seq Scan on serial_no  (cost=0.00..1.05 rows=1 width=4)
>         Filter: (("no")::text = 'WX1234'::text)