X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E02414763C2 for ; Tue, 29 Apr 2003 07:12:34 -0400 (EDT) Received: from rtlocal.trade-india.com (unknown [203.196.129.235]) by postgresql.org (Postfix) with SMTP id F3D514763C2 for ; Tue, 29 Apr 2003 07:11:53 -0400 (EDT) Received: (qmail 21477 invoked from network); 29 Apr 2003 11:11:56 -0000 Received: from unknown (HELO system67.trade-india-local.com) (unknown) by unknown with SMTP; 29 Apr 2003 11:11:56 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: Shridhar Daithankar , pgsql-performance@postgresql.org Subject: Re: Is 292 inserts/sec acceptable performance ? Date: Tue, 29 Apr 2003 16:42:24 +0530 User-Agent: KMail/1.5 References: <200304291231.09842.mallah@trade-india.com> <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in> In-Reply-To: <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200304291642.24543.mallah@trade-india.com> X-Spam-Status: No, hits=-31.8 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,NO_DNS_FOR_FROM, QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, SIGNATURE_SHORT_SPARSE,USER_AGENT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/238 X-Sequence-Number: 1744 Yeah even 1 feild can be given in the NOT EXISTS part. bUt i vaugely recally tom saying that it does not matter and internally its converted to "select * form tab" from, correct me if i am recalling wrong. in anycase the CREATE TABLE part is working fine ie in 3 mins the select and table creation is over. Is the continuously entering data slowing down the NO EXISTS part ? in any case that inserts are supposed to be invisible to the NOT EXISTS part i guess. regds mallah. On Tuesday 29 Apr 2003 12:55 pm, Shridhar Daithankar wrote: > On Tuesday 29 April 2003 12:31, Rajesh Kumar Mallah wrote: > > Hi Can anyone tell if the case below is an acceptable > > performance ? > > > > I have a query that returns data and creates a table > > in 3 mins approx. This query is optimised and uses appropriate > > indexes for the NOT EXISTS part. > > > > CREATE TABLE t_a as SELECT > > email,country_code,city,title1,fname1,mname1,lname1,website,address,sourc > >e, ifimporter, ifexporter,ifservice,ifmanu,creation_date from > > general.email_bank_import where not exists (select * from > > general.profile_master where > > email=general.email_bank_import.email) ; > > SELECT > > Time: 174637.31 ms (3 mins Approx) > > > > > > > > The problem is when i try to INSERT the data into another table > > it takes 23 mins Apprx to inser 412331 records the same query. > > > > I am providing the various details below: > > > > tradein_clients=# INSERT INTO general.profile_master > > (email,country_code,city,title1,fname1,mname1,lname1,website,address,sour > >ce ,ifimporter,ifexporter, ifservice, ifmanu,creation_date) SELECT > > email,country_code, > > city,title1,fname1,mname1,lname1,website,address,source,ifimporter,ifexpo > >rt er,ifservice, ifmanu,creation_date from general.email_bank_import > > where not exists (select * from general.profile_master where > > email=general.email_bank_import.email) ; > > INSERT 0 412331 > > Time: 1409510.63 ms > > I am not sure if this would help but why you have to use all the fields in > not exists clause? How about not exists for a name or profile_id? Would it > be any faster > > I assume if there are two records with half the info same, then not exists > for 1 field with index would be significantly faster than 10 fields. > > HTH > > Shridhar > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.