X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 17DD0475ED4 for ; Fri, 28 Mar 2003 10:59:55 -0500 (EST) Received: from wolff.to (wolff.to [66.93.249.74]) by postgresql.org (Postfix) with SMTP id 697AD475E77 for ; Fri, 28 Mar 2003 10:59:54 -0500 (EST) Received: (qmail 10916 invoked by uid 500); 28 Mar 2003 15:59:33 -0000 Date: Fri, 28 Mar 2003 09:59:33 -0600 From: Bruno Wolff III To: Jeremiah Elliott Cc: pgsql-performance@postgresql.org Subject: Re: slow query - where not in Message-ID: <20030328155933.GA10761@wolff.to> Mail-Followup-To: Jeremiah Elliott , pgsql-performance@postgresql.org References: <200303280938.50382.jelliott@artcobell.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200303280938.50382.jelliott@artcobell.com> User-Agent: Mutt/1.3.25i X-Spam-Status: No, hits=-38.0 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_MUTT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/182 X-Sequence-Number: 1480 On Fri, Mar 28, 2003 at 09:38:50 -0600, Jeremiah Elliott wrote: > here is the query that is killing me: > > select shoporder from sodetailtabletrans where shoporder not in(select > shoporder from soheadertable) This will probably work better in 7.4. For now there are several ways to rewrite this query. If there are no null values for shoporder in soheadertable or sodetailtabletrans you can use not exists instead of not in: select shoporder from sodetailtabletrans where shoporder not exists(select shoporder from soheadertable) You can use set difference to calculate the result: select shoporder from sodetailtabletrans except all select shoporder from soheadertable If there are no null values for shoporder in one of sodetailtabletrans or soheadertable you can user an outer join with a restriction that limits the rows of interest to those that don't match: select sodetailtabletrans.shoporder from sodetailtabletrans left join soheadertable using (shoporder) where soheadertable.shoporder is null