X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 95599476022 for ; Tue, 29 Apr 2003 03:26:16 -0400 (EDT) Received: from www.pspl.co.in (www.pspl.co.in [202.54.11.65]) by postgresql.org (Postfix) with ESMTP id 61F76476022 for ; Tue, 29 Apr 2003 03:25:39 -0400 (EDT) Received: (from root@localhost) by www.pspl.co.in (8.11.6/8.11.6) id h3T7PcL27972 for ; Tue, 29 Apr 2003 12:55:38 +0530 Received: from daithan.intranet.pspl.co.in (daithan.intranet.pspl.co.in [192.168.7.161]) by www.pspl.co.in (8.11.6/8.11.0) with ESMTP id h3T7Pc427967 for ; Tue, 29 Apr 2003 12:55:38 +0530 From: Shridhar Daithankar To: pgsql-performance@postgresql.org Subject: Re: Is 292 inserts/sec acceptable performance ? Date: Tue, 29 Apr 2003 12:55:15 +0530 User-Agent: KMail/1.5.1 References: <200304291231.09842.mallah@trade-india.com> In-Reply-To: <200304291231.09842.mallah@trade-india.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in> X-Spam-Status: No, hits=-37.6 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,NO_DNS_FOR_FROM, QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, USER_AGENT_KMAIL 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/236 X-Sequence-Number: 1742 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,source, >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,source >,ifimporter,ifexporter, ifservice, ifmanu,creation_date) SELECT > email,country_code, > city,title1,fname1,mname1,lname1,website,address,source,ifimporter,ifexport >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