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.
