pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org
Subject: Re: Is 292 inserts/sec acceptable performance ?
Date: Tue, 29 Apr 2003 12:55:15 +0530
Message-ID: <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in> (raw)
In-Reply-To: <200304291231.09842.mallah@trade-india.com>
References: <200304291231.09842.mallah@trade-india.com>

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




view thread (6+ messages)  latest in thread

Message-ID: <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in>
Permalink:  ../200304291255.15558.shridhar_daithankar@nospam.persistent.co.in/
Also on:    postgresql.org/message-id/200304291255.15558.shridhar_daithankar@nospam.persistent.co.in

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: shridhar_daithankar@persistent.co.in
  Subject: Re: Is 292 inserts/sec acceptable performance ?
  In-Reply-To: <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in>
  References: <200304291231.09842.mallah@trade-india.com> <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox