From: | "assaf" <assaf_lehr(at)yahoo(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5165: Poor performance with Left-join where right side does not exist |
Date: | 2009-11-04 17:44:05 |
Message-ID: | 200911041744.nA4Hi5l1028570@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5165
Logged by: assaf
Email address: assaf_lehr(at)yahoo(dot)com
PostgreSQL version: 8.37
Operating system: linux
Description: Poor performance with Left-join where right side does
not exist
Details:
A
category | idB | multiple-values
B
category | idB | multiple-values
--------------
select B.idB , A.idB from B left join A on B.idB = A.idB and
A.category=B.category
where A.idB is null [and A.category=202] limit 10
--------------
I have indexes on all columns and thier permutations.
I needed merge-join here and expected brief results ,as it is easy to find
not-nulls running on both indexes.
My DB is quite big (20M items) and the result was supposed to be 0.5M items.
I stopped waiting after 18 hours.
There is workaround , select B.idB from B where category=202 and idB not in
(select distinct idB from A where category=202);
It finishes in good time(10min) , but it`s totally a waste to use subplan
here.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2009-11-04 20:35:32 | Re: BUG #5165: Poor performance with Left-join where right side does not exist |
Previous Message | Heikki Linnakangas | 2009-11-04 12:53:34 | Re: BUG #4961: pg_standby.exe crashes with no args |