X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id DD42D475ED4 for ; Fri, 28 Mar 2003 10:42:49 -0500 (EST) Received: from geek.artcobell.com (unknown [63.114.209.181]) by postgresql.org (Postfix) with SMTP id 0F19C475E77 for ; Fri, 28 Mar 2003 10:42:48 -0500 (EST) Received: (qmail 15788 invoked from network); 28 Mar 2003 15:38:52 -0000 Received: from unknown (HELO localhost) (127.0.0.1) by localhost.uu.net with SMTP; 28 Mar 2003 15:38:52 -0000 From: Jeremiah Elliott Reply-To: jelliott@artcobell.com Organization: Artco-Bell Corp. To: pgsql-performance@postgresql.org Subject: slow query - where not in Date: Fri, 28 Mar 2003 09:38:50 -0600 User-Agent: KMail/1.5 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200303280938.50382.jelliott@artcobell.com> X-Spam-Status: No, hits=-5.8 required=5.0 tests=BAYES_10,USER_AGENT version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200303/181 X-Sequence-Number: 1479 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. soheadertable - 5104 rows CREATE TABLE "soheadertable" ( "shoporder" numeric(10,0) NOT NULL, "initrundate" date, "actualrundate" date, "processedminutes" numeric(10,0), "starttime" timestamptz, "endtime" timestamptz, "line" int4, "personcount" numeric(10,0), "product" varchar(15), "qtytomake" numeric(10,3), "linestatus" numeric(2,0) DEFAULT 1, "finishlinestatus" numeric(2,0) DEFAULT 1, "qtyinqueue" numeric(10,3), "lastcartonprinted" numeric(10,0), "qtydone" int8, "warehouse" text, "rescheduledate" date, "calculateddatetorun" date ); CREATE UNIQUE INDEX "shoporder_soheadertable_ukey" ON "soheadertable" ("shoporder"); sodetailtabletrans - 31494 rows CREATE TABLE "sodetailtabletrans" ( "shoporder" numeric(10,0) NOT NULL, "soseq" numeric(5,0) NOT NULL, "product" char(15) NOT NULL, "qtyqueued" numeric(17,2), "qtyneeded" numeric(17,2), "qtyallocated" numeric(17,2), "qtyused" numeric(17,2), "linestatus" numeric(2,0) DEFAULT 1, "unitsperenditem" numeric(10,1), CONSTRAINT "sodetailtrans_pk" PRIMARY KEY ("shoporder", "soseq") -Jeremiah Elliott jelliott@artcobell.com );