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@xxxxxxxxxxxxxxx> wrote: > From: Ivan Sergio Borgonovo <mail@xxxxxxxxxxxxxxx> > Subject: Re: [GENERAL] eliminating records not in (select id ... so SLOW? > To: > Cc: "PostgreSQL" <pgsql-general@xxxxxxxxxxxxxx> > Date: Thursday, July 31, 2008, 11:01 PM > On Thu, 31 Jul 2008 14:59:29 -0700 (PDT) > Lennin Caro <lennin.caro@xxxxxxxxx> 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@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
