Skip site navigation (1) Skip section navigation (2)

Re: Is 292 inserts/sec acceptable performance ?

From: Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Is 292 inserts/sec acceptable performance ?
Date: 2003-04-29 07:25:15
Message-ID: 200304291255.15558.shridhar_daithankar@nospam.persistent.co.in (view raw or flat)
Thread:
Lists: pgsql-performance
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


In response to

Responses

pgsql-performance by date

Next:From: Rob MesserDate: 2003-04-29 09:03:01
Subject: Optimizer not using index when it should
Previous:From: Rajesh Kumar MallahDate: 2003-04-29 07:01:09
Subject: Is 292 inserts/sec acceptable performance ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group