The following bug has been logged online: Bug reference: 5165 Logged by: assaf Email address: assaf_l...@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. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
