X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id B078E475E77 for ; Fri, 28 Mar 2003 11:50:09 -0500 (EST) Received: from mail.gmx.net (mail.gmx.net [213.165.64.20]) by postgresql.org (Postfix) with SMTP id 8527447580B for ; Fri, 28 Mar 2003 11:50:08 -0500 (EST) Received: (qmail 6682 invoked by uid 65534); 28 Mar 2003 16:50:12 -0000 Received: from chello062178186201.1.15.tuwien.teleweb.at (EHLO beeblebrox) (62.178.186.201) by mail.gmx.net (mp014-rz3) with SMTP; 28 Mar 2003 17:50:12 +0100 Message-ID: <028e01c2f54a$9af3d010$3201a8c0@beeblebrox> From: "Michael Paesold" To: "Bruno Wolff III" , "Jeremiah Elliott" Cc: References: <200303280938.50382.jelliott@artcobell.com> <20030328155933.GA10761@wolff.to> Subject: Re: slow query - where not in Date: Fri, 28 Mar 2003 17:53:46 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Spam-Status: No, hits=-25.8 required=5.0 tests=BAYES_20,EMAIL_ATTRIBUTION,QUOTED_EMAIL_TEXT,REFERENCES, REPLY_WITH_QUOTES 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/184 X-Sequence-Number: 1482 Bruno Wolff III wrote: > Jeremiah Elliott wrote: > > here is the query that is killing me: > > > > select shoporder from sodetailtabletrans where shoporder not in(select > > shoporder from soheadertable) > > 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) I think this should rather be: SELECT shoporder FROM sodetailtabletrans WHERE NOT EXISTS ( SELECT 1 FROM soheadertable WHERE soheadertable.shoporder = sodetailtabletrans.shoporder ) Regards, Michael Paesold