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)
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
