Received: from desertmoon.com (qmailr@desertmoon.com [198.182.124.2]) by hub.org (8.9.1/8.9.1) with SMTP id JAA02485 for ; Tue, 24 Nov 1998 09:52:39 -0500 (EST) (envelope-from pierre@desertmoon.com) From: pierre@desertmoon.com Received: (qmail 28760 invoked by uid 100); 24 Nov 1998 14:57:56 -0000 Message-ID: <19981124145756.28759.qmail@desertmoon.com> Date: 24 Nov 1998 07:57:56 -0700 Subject: Re: [SQL] MINUS and slow 'not in' To: herouth@oumail.openu.ac.il (Herouth Maoz) Date: Tue, 24 Nov 1998 07:57:56 -0700 (MST) Cc: pgsql-sql@postgreSQL.org In-Reply-To: from "Herouth Maoz" at Nov 24, 98 11:14:42 am X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit > > At 6:53 +0200 on 24/11/98, pierre wrote: > > > I then tried using a 'not in' clause. > > > > select * from A where user_id not in (select * from B); > > > > This is VERY slow, and examining the explain output tells me that it will > > use the user_id index for table B, but a sequential scan of A even though > > A has an index for the user_id column. > > First, I assume you meant "select user_id from B", not "select *", or > something is very strange here. > > You may try to convert the NOT IN to a NOT EXISTS clause, and see if it > improves anything, but it will still require a sequential search. > > SELECT * FROM A > WHERE NOT EXISTS ( > SELECT * FROM B > WHERE B.user_id = A.user_id > ); > > By the way, if you have any specific criteria on A, besides the NOT EXISTS > or NOT IN, they may cause an index scan on A as well. > Ok...remember that I have table A with 40k rows, and B with 2k. What I want to really get out of the query are 2k rows from A that are not contained in B. After reading your email, I thought about using a cursor and only fetching the first 2k rows that match the query. This helped tremendously in that it didn't try and return all 38k rows. However I now need to take the results of the fetch and dump it into table B. How can one use fetch to insert? I've tried... insert into B fetch 2000 from fubar; Which just gives a parser error. There is very little documentation on cursors written up that I can find. I've even searched the email archives. Ideas? -=pierre