pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: 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 16:42:24 +0530
Message-ID: <200304291642.24543.mallah@trade-india.com> (raw)
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>


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.




view thread (6+ messages)  latest in thread

Message-ID: <200304291642.24543.mallah@trade-india.com>
Permalink:  ../200304291642.24543.mallah@trade-india.com/
Also on:    postgresql.org/message-id/200304291642.24543.mallah@trade-india.com

 · 

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: mallah@trade-india.com, shridhar_daithankar@persistent.co.in
  Subject: Re: Is 292 inserts/sec acceptable performance ?
  In-Reply-To: <200304291642.24543.mallah@trade-india.com>
  References: <200304291231.09842.mallah@trade-india.com> <200304291255.15558.shridhar_daithankar@nospam.persistent.co.in> <200304291642.24543.mallah@trade-india.com>

* 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