Re: Select Statement Hang...

From: "Chad Thompson" <chad(at)weblinkservices(dot)com>
To: "ttzhang" <ttzhang(at)uclink(dot)berkeley(dot)edu>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Select Statement Hang...
Date: 2003-07-21 18:03:32
Message-ID: 005001c34fb2$6674a7c0$32021aac@chad
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

> I was trying to set up a foreign key for an id field but found out that
some
> of the ids are not in the referenced table. To look things up, I tried
>
> select id from btable where id not in (select id from atable)
>
> The query just hang..... My tables are pretty big (200, 000?) can anyone
> help me to pinpoint the problem?
>
> Thanks!
>

You might try

select id
from btable
where not exists
(select id from atable)

I have found that this performs better.
You should also make sure that you've vacuumed your tables.

If this doesnt help, post the results of

explain analyze
select id
from btable
where not exists
(select id from atable)

HTH
Chad

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Dmitry Tkach 2003-07-21 18:03:50 Re: Locking tables
Previous Message Allan Berger 2003-07-21 17:58:32 Re: Locking tables