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

ok try this

delete from catalog_categoryitem where not exists (select id from catalog_items where catalog_items.ItemID = catalog_categoryitem.ItemID);

--- On Thu, 7/31/08, Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> wrote:

> From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
> Subject: Re: [GENERAL] eliminating records not in (select id ... so SLOW?
> To:
> Cc: "PostgreSQL" <pgsql-general(at)postgresql(dot)org>
> Date: Thursday, July 31, 2008, 11:01 PM
> 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
>
>
> --
> Sent via pgsql-general mailing list
> (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general