X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 4B683D1DD59 for ; Wed, 18 Feb 2004 06:52:29 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 89358-08 for ; Wed, 18 Feb 2004 02:52:28 -0400 (AST) Received: from filer (c-24-6-183-218.client.comcast.net [24.6.183.218]) by svr1.postgresql.org (Postfix) with ESMTP id 2F2DFD1D25D for ; Wed, 18 Feb 2004 02:52:27 -0400 (AST) Received: from localhost (localhost [127.0.0.1]) (uid 1000) by filer with local; Tue, 17 Feb 2004 22:52:26 -0800 Date: Tue, 17 Feb 2004 22:52:26 -0800 From: Kevin Brown To: pgsql-performance Subject: Re: UPDATE with subquery too slow Message-ID: <20040218065225.GB3090@filer> Mail-Followup-To: Kevin Brown , pgsql-performance References: <00ed01c3f552$ec054e80$c300000a@caliente> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline In-Reply-To: <00ed01c3f552$ec054e80$c300000a@caliente> Organization: Frobozzco International User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/206 X-Sequence-Number: 5730 Eric Jain wrote: > I can't get the following statement to complete with reasonable time. > I've had it running for over ten hours without getting anywhere. I > suspect (hope) there may be a better way to accomplish what I'm trying > to do (set fields containing unique values to null): [...] > Using EXISTS rather than IN (I'm using 7.4-RC2, not sure if IN queries > were already improved in this release): > > UPDATE requests > SET session = NULL > WHERE NOT EXISTS > ( > SELECT r.session > FROM requests r > WHERE > r.session = session > AND NOT r.id = id > ); I suppose you could try: UPDATE requests SET session = NULL WHERE EXISTS ( SELECT r.session FROM requests r WHERE r.session = session GROUP BY r.session HAVING count(*) = 1 ); but I don't know that you'll get much different results than your version. -- Kevin Brown kevin@sysexperts.com