From: | Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it> |
---|---|
To: | |
Cc: | PostgreSQL <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: eliminating records not in (select id ... so SLOW? |
Date: | 2008-07-31 23:01:12 |
Message-ID: | 20080801010112.77832227@dawn.webthatworks.it |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew | 2008-07-31 23:03:35 | Re: hibernate nativequery and uuid |
Previous Message | Douglas McNaught | 2008-07-31 22:46:34 | Re: hibernate nativequery and uuid |