Received: from jupiter.hub.org (jupiter.hub.org [64.49.215.5]) by postgresql.org (Postfix) with ESMTP id 092CB4764AB for ; Wed, 6 Mar 2002 16:04:26 -0500 (EST) Received: (from news@localhost) by jupiter.hub.org (8.11.6/8.11.6) id g26KsHu24936 for pgsql-sql@postgresql.org; Wed, 6 Mar 2002 14:54:17 -0600 (CST) (envelope-from news) From: "Llew" X-Newsgroups: comp.databases.postgresql.sql Subject: Re: where not exists Date: Wed, 6 Mar 2002 20:54:15 -0000 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 59 Message-ID: References: <7C760DAA511DC74B99E7D22189F786F1906D98@MAIL01.stbernard.com> Reply-To: "Llew" X-Complaints-To: news@hub.org X-Priority: 3 X-MSMail-Priority: Normal 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-Archive-Number: 200203/138 X-Sequence-Number: 7018 Dear Jie Liang, The common case is that there will only be a few or no orphans. Won't recreateing and reindicing this massive table (table a) take ages and known my db out of commission for everyone else? One of the reasons why I do things is two steps is that this allows me archive the deleted items. This is, alas, a common (read daily) operation / query. Leo "Jie Liang" wrote in message news:7C760DAA511DC74B99E7D22189F786F1906D98@MAIL01.stbernard.com... > set operation especially NOT EXIST is very slow(for big table), > I recommand you use a few queries for your propose: > 1. select * into c from a where join1=b.join1 and join2=b.join2; > 2. truncate table a; > 3. insert into a select * from c; > 4. drop table c; > > You don't need index c since you use full table scan anyway. > > What is the best way of removing rows which are not in another table? > > I have these two tables each with millions of rows/tuples. > They are joined on two fields: > CREATE TABLE a > ( > join1 OID, > join2 OID, > --a fair number of other fields > ..... > ) > CREATE TABLE b > ( > join1 OID, > join2 OID, > --a fair number of other fields > ..... > ) > There are indices on both of them (on "join1, join2"). > At the moment, I am doing > > 1) get items which are orphaned in a. > CREATE TEMP TABLE orphans as > SELECT join1, join2 > FROM a > WHERE NOT EXISTS > ( > SELECT * > FROM b > WHERE > a.join1 = b.join1 AND > a.join2 = b.join2 > ) > 2) DELETE FROM a where orphans.join1 = a.join1 and orphans.join2=a.join2 > 3) DROP TABLE orphans > > This is very slow. Is there a better way?