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 Scan on serial_no (cost=0.00..1.04 rows=1 width=4)
Filter: (("no")::text = 'WX1234'::text)
-> Seq Scan on 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)