Re: "NOT IN" predicate hangs result

On Thu, 11 Apr 2002 12:01:29 -0400
"Rick Szeto" <rszeto@csi.ca> wrote:
> 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?

Yes; IN doesn't hang Postgres, it is just very slow. It is documented here:

    http://www.ca.postgresql.org/docs/faq-english.html#4.22

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC