Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 469F7475F31 for ; Sat, 24 Aug 2002 17:11:44 -0400 (EDT) Received: from news.hub.org (news.hub.org [64.49.215.80]) by postgresql.org (Postfix) with ESMTP id ABDA1475F24 for ; Sat, 24 Aug 2002 17:11:43 -0400 (EDT) Received: by news.hub.org (Postfix, from userid 8) id 1B2AF381911; Sat, 24 Aug 2002 17:11:43 -0400 (EDT) From: "OU" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Separating data sets in a table Date: Sat, 24 Aug 2002 23:52:23 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 34 Message-ID: References: X-Complaints-To: news@hub.org X-Newsreader: Microsoft Outlook Express 6.00.2600.0000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 To: pgsql-sql@postgresql.org X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/383 X-Sequence-Number: 9229 "Andreas Tille" a écrit dans le message de news: Pine.LNX.4.44.0208221126490.1478-100000@wr-linux02.rki.ivbb.bund.de... ... > I tried to do the following approach: > > CREATE LOCAL TEMPORARY TABLE ImportOK () INHERITS (Import) ; > > INSERT INTO ImportOK SELECT * FROM Import i > INNER JOIN Ref r ON i.Id = r.Id; > > DELETE FROM Import WHERE Id IN (SELECT Id FROM ImportOK) ; ... > Unfortunately the latest statement is so terribly slow that I can't > imagine that there is a better way to do this. > You must use EXISTS if you work with big tables. EXISTS use indexes, and IN use temporary tables. -- this change nothing for IN : CREATE INDEX import_id_index ON import(id); CREATE INDEX import_ok_id_index ON import_ok(id); -- slow : -- DELETE FROM import WHERE id IN (SELECT id FROM import_ok) ; DELETE FROM import WHERE EXISTS ( SELECT id FROM import_ok AS ok WHERE ok.id = import.id );