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)
