From: Zulq Alam <zulq@orange.net>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: indexing for left join
Date: Sat, 21 Jan 2006 13:51:56 +0000
Message-ID: <43D23C7C.1000108@orange.net> (raw)
In-Reply-To: <43CFB325.9060306@numerixtechnology.de>
References: <43CFB325.9060306@numerixtechnology.de>
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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: zulq@orange.net
Subject: Re: indexing for left join
In-Reply-To: <43D23C7C.1000108@orange.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox