The following bug has been logged online:
Bug reference: 5165
Logged by: assaf
Email address: assaf_lehr@yahoo.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.
