X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 4F72BD1B514 for ; Wed, 10 Sep 2003 03:11:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 48583-08 for ; Wed, 10 Sep 2003 00:11:14 -0300 (ADT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id C8966D1B51F for ; Wed, 10 Sep 2003 00:11:13 -0300 (ADT) Received: (qmail 18811 invoked by uid 500); 10 Sep 2003 03:17:37 -0000 Date: Tue, 9 Sep 2003 22:17:37 -0500 From: Bruno Wolff III To: Jainendra Kumar P Cc: pgsql-sql@postgresql.org Subject: Re: How can I optimize this query Message-ID: <20030910031737.GA18726@wolff.to> Mail-Followup-To: Jainendra Kumar P , pgsql-sql@postgresql.org References: <284E845CC8F394409203F065D218205C044896@mysmsg03.ad.infosys.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <284E845CC8F394409203F065D218205C044896@mysmsg03.ad.infosys.com> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200309/78 X-Sequence-Number: 15056 On Tue, Sep 09, 2003 at 13:39:10 +0530, Jainendra Kumar P wrote: > I have the following query > > UPDATE accumulator1 SET accumulator1.status = "User Excluded" > WHERE accumulator1.name NOT IN > (SELECT DISTINCT accumulator1.name > FROM accumulator1, diaaffectedstmts > WHERE diaaffectedstmts.stmt like '*'+ accumulator1.name +'*' and > diaaffectedstmts.xref_type <> > "D"); > > How can I optimize this query? It will probably work better in 7.4. But for now you might try replacing NOT IN with NOT EXISTS. As long as accumulator1.name isn't going to be NULL you should be able to rewrite the subselect to find rows where name in the subselect matches name in the outer select.