Re: "NOT IN" predicate hangs result

It is generally known that using EXISTS will be much faster than IN for the sort
of query you describe. Seems to come up regularly on this very list in fact.

Wes

"Rick Szeto" <rszeto%csi(dot)ca(at)interlock(dot)lexmark(dot)com> on 04/11/2002 12:01:29 PM

To: pgsql-general%postgresql(dot)org(at)interlock(dot)lexmark(dot)com
cc: (bcc: Wesley Sheldahl/Lex/Lexmark)
Subject: [GENERAL] "NOT IN" predicate hangs result

Hi all,
I recently tried this select statement and it hung my tool:

1) select count(*) from user_profile where address_id not in (select
address_id from address);

Originally, I just thought that it was slow, so I left it over night and when I
can back the next morning it
just hung there(eating up CPU cycles). I looked in Celko's book and then tried
this and it worked(quite fast):

2) select count(*) from user_profile up where not exists (select * from
address addr where up.address_id = addr.address_id);

Is this a known problem?

Thanks
Rick

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org