X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 20CF6475458 for ; Wed, 30 Apr 2003 03:38:42 -0400 (EDT) Received: from rtlocal.trade-india.com (mail-relay.trade-india.com [203.196.129.235]) by postgresql.org (Postfix) with SMTP id BF899474E42 for ; Wed, 30 Apr 2003 03:38:32 -0400 (EDT) Received: (qmail 23007 invoked from network); 30 Apr 2003 07:39:16 -0000 Received: from unknown (HELO system67.trade-india-local.com) (unknown) by unknown with SMTP; 30 Apr 2003 07:39:16 -0000 From: Rajesh Kumar Mallah Organization: Infocom Network Limited To: Tom Lane Subject: Re: Is 292 inserts/sec acceptable performance ? Date: Wed, 30 Apr 2003 13:09:53 +0530 User-Agent: KMail/1.5 Cc: pgsql-performance@postgresql.org References: <200304291231.09842.mallah@trade-india.com> <26231.1051624859@sss.pgh.pa.us> In-Reply-To: <26231.1051624859@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200304301309.53150.mallah@trade-india.com> X-Spam-Status: No, hits=-33.1 required=5.0 tests=BAYES_01,EMAIL_ATTRIBUTION,IN_REP_TO,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/255 X-Sequence-Number: 1761 Ooops Sorry , Actually the query finished in approx 4 mins not 23 mins. That performance must have been under some crazy circumstances. So the insert Rate now is 1608 inserts/sec not 292 as stated earlier. Here is the EXPLAIN ANALYZE anyway tradein_clients=# begin work;EXPLAIN analyze 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,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) ; rollback; BEGIN Time: 993.07 ms +---------------------------------------------------------------------------------------------------------------------------------------------------------+ | QUERY PLAN | +---------------------------------------------------------------------------------------------------------------------------------------------------------+ | Hash Join (cost=8.07..2395887.30 rows=279296 width=129) (actual time=2.56..151083.30 rows=394646 loops=1) | | Hash Cond: ("outer".country = "inner".name) | | -> Seq Scan on email_bank a (cost=0.00..2390293.31 rows=279296 width=109) (actual time=0.36..41475.08 rows=394646 loops=1) | | Filter: (NOT (subplan)) | | SubPlan | | -> Index Scan using profile_master_email on profile_master (cost=0.00..31.66 rows=7 width=678) (actual time=0.05..0.05 rows=0 loops=558731) | | Index Cond: (email = $0) | | -> Hash (cost=7.46..7.46 rows=246 width=20) (actual time=1.11..1.11 rows=0 loops=1) | | -> Seq Scan on countries b (cost=0.00..7.46 rows=246 width=20) (actual time=0.06..0.73 rows=246 loops=1) | | Total runtime: 196874.70 msec | +---------------------------------------------------------------------------------------------------------------------------------------------------------+ (10 rows) Time: 198905.62 ms ROLLBACK Time: 1481.41 ms Regds mallah. On Tuesday 29 Apr 2003 7:30 pm, Tom Lane wrote: > Rajesh Kumar Mallah writes: > > Hi Can anyone tell if the case below is an acceptable > > performance ? > > Not with that info. Could we see EXPLAIN ANALYZE results for both > the faster and slower cases? > > regards, tom lane > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- 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.