X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 73B10476356 for ; Fri, 28 Mar 2003 11:20:30 -0500 (EST) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id C122747634B for ; Fri, 28 Mar 2003 11:20:29 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 3E9611381D8; Fri, 28 Mar 2003 11:20:32 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18ywal-0005W4-00; Fri, 28 Mar 2003 11:20:31 -0500 To: jelliott@artcobell.com Cc: pgsql-performance@postgresql.org Subject: Re: slow query - where not in References: <200303280938.50382.jelliott@artcobell.com> In-Reply-To: <200303280938.50382.jelliott@artcobell.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 28 Mar 2003 11:20:29 -0500 Message-ID: <87u1dn31ki.fsf@stark.dyndns.tv> Lines: 36 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Spam-Status: No, hits=-38.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT_GNUS_UA 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/183 X-Sequence-Number: 1481 Jeremiah Elliott writes: > here is the query that is killing me: > > select shoporder from sodetailtabletrans where shoporder not in(select > shoporder from soheadertable) > > This is just an example query. Any time I use 'where not in(' it takes several > hours to return a resultset. The postgres version is 7.2.3 although I have > tried it on my test server which has 7.3 on it and it runs just as slow. The > server is a fast server 2GHz with a gig of ram. I have tried several > differant index setups but nothing seems to help. This should be improved with 7.4, however there are some other things you can try now. try SELECT shoporder FROM sodetailtabletrans WHERE NOT EXISTS ( SELECT 1 FROM soheadertable WHERE shoporder = sodetailtabletrans.shoporder ) or else try something like SELECT a.shoporder FROM sodetailtabletrans as a LEFT OUTER JOIN soheadertable as b ON (a.shoporder = b.shoporder) WHERE b.shoporder IS NULL -- greg