Re: eliminating records not in (select id ... so SLOW?

[Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>]

On Thu, 31 Jul 2008 14:59:29 -0700 (PDT)
Lennin Caro <lennin(dot)caro(at)yahoo(dot)com> wrote:

> > The box is a 2x dual core Xeon (below 2GHz) with 4Gb ram.
> > Default debian etch setup.

> you recently run vacuum ? 

The tables are pretty stable. I think no more than 20 records were
modified (update/insert/delete) during the whole history of the 2
tables.

autovacuum is running regularly.

The actual query running is:

begin;
 create index catalog_categoryitem_ItemsID_index on
   catalog_categoryitem using btree (ItemID);
 delete from catalog_categoryitem
   where ItemID not in (select ItemID from catalog_items);
commit;

That's what came back
Timing is on.
BEGIN
Time: 0.198 ms
CREATE INDEX
Time: 3987.991 ms

The query is still running...

As a reminder catalog_categoryitem should contain less than 1M
record.
catalog_items should contain a bit more than 600K record where
ItemID is unique (a pk actually).
PostgreSQL comes from the default install from Debian etch (8.1.X).
It's configuration hasn't been modified.

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it