pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is 292 inserts/sec acceptable performance ?
6+ messages / 4 participants
[nested] [flat]

* Is 292 inserts/sec acceptable performance ?
@ 2003-04-29 07:01  Rajesh Kumar Mallah <mallah@trade-india.com>
  0 siblings, 2 replies; 6+ messages in thread

From: Rajesh Kumar Mallah @ 2003-04-29 07:01 UTC (permalink / raw)
  To: pgsql-performance



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,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) ;
INSERT 0 412331
Time: 1409510.63 ms


The table destination general.profile_master in which 
data is being inserted was already having 184424 records 
before the INSERT the VACUUM FULL ANALZYE VERBOSE output was:

tradein_clients=# VACUUM FULL VERBOSE ANALYZE profile_master ;
INFO:  --Relation general.profile_master--
INFO:  Pages 9161: Changed 0, reaped 8139, Empty 0, New 0; Tup 184424: Vac 72, 
Keep/VTL 0/0, UnUsed 118067, MinLen 154, MaxLen 2034; Re-using: Free/Avail. 
Space 708064/337568; EndEmpty/Avail. Pages 0/1669.
        CPU 0.17s/0.03u sec elapsed 0.21 sec.
INFO:  Index profile_master_email: Pages 8921; Tuples 184424: Deleted 72.
        CPU 0.15s/0.21u sec elapsed 0.37 sec.
INFO:  Index profile_master_profile_id_pkey: Pages 1295; Tuples 184424: 
Deleted 72.
        CPU 0.03s/0.10u sec elapsed 0.16 sec.
INFO:  Rel profile_master: Pages: 9161 --> 9161; Tuple(s) moved: 0.
        CPU 0.44s/0.98u sec elapsed 15.79 sec.
INFO:  --Relation pg_toast.pg_toast_163041602--
INFO:  Pages 31: Changed 0, reaped 1, Empty 0, New 0; Tup 187: Vac 0, Keep/VTL 
0/0, UnUsed 2, MinLen 50, MaxLen 2034; Re-using: Free/Avail. Space 
24800/24788; EndEmpty/Avail. Pages 0/30.
        CPU 0.00s/0.00u sec elapsed 3.04 sec.
INFO:  Index pg_toast_163041602_index: Pages 2; Tuples 187: Deleted 0.
        CPU 0.00s/0.00u sec elapsed 0.49 sec.
INFO:  Rel pg_toast_163041602: Pages: 31 --> 31; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.

INFO:  Analyzing general.profile_master
VACUUM
It was already vacuumed once.

Index Info: Only two indexes were existing 

tradein_clients=# \d profile_master
   Table "general.profile_master"
+--------------------+------------------------+-------
|       Column       |          Type          |                                                    
+--------------------+------------------------+-------
| profile_id         | integer                |        
| userid             | integer                |                                                                         
| co_name            | character varying(100) |                                    
| address            | text                   |                                                                         
| pincode            | character varying(20)  |                                                                         
| city               | character varying(50)  |                                                                         
| country_code       | character varying(2)   |                                                                         
| phone_no           | character varying(100) |                                                                         
| fax_no             | character varying(100) |                                                                         
| email              | character varying(100) |                                                                         
| website            | character varying(100) |                                                                         
| title1             | character varying(15)  |                                                                         
| fname1             | character varying(200) |                                                                         
| mname1             | character varying(30)  |                                                                         
| lname1             | character varying(30)  |                                                                         
| desg1              | character varying(100) |                                                                         
| mobile             | character varying(20)  |                                                                         
| title2             | character varying(15)  |                                                                         
| fname2             | character varying(30)  |                                                                         
| mname2             | character varying(30)  |                                                                         
| lname2             | character varying(30)  |                                                                         
| desg2              | character varying(100) |                                                                         
| mobile2            | character varying(20)  |                                                                         
| co_branches        | character varying(100) |                                                                         
| estd               | smallint               |                                                                         
| staff              | integer                |                                                                         
| prod_exp           | text                   |                                                                         
| prod_imp           | text                   |                                                                         
| prod_manu          | text                   |                                                                         
| prod_serv          | text                   |                                                                         
| ifexporter         | boolean                | not null                                                                
| ifimporter         | boolean                | not null                                                                
| ifservice          | boolean                | not null                                                                
| ifmanu             | boolean                | not null                                                                
| bankers            | character varying(255) |                                                                         
| imp_exp_code       | character varying(100) |                                                                         
| memb_affil         | character varying(255) |                                                                         
| std_cert           | character varying(255) |                                                                         
| branch_id          | integer                |                                                                         
| area_id            | integer                |                                                                         
| annual_turn        | numeric                |                                                                         
| annual_currency    | character varying(5)   |                                                                         
| exp_turn           | numeric                |                                                                         
| exp_currency       | character varying(5)   |                                                                         
| imp_turn           | numeric                |                                                                         
| imp_currency       | character varying(5)   |                                                                         
| creation_date      | integer                | not null                                                                
| profile_status     | character varying(10)  |                                                                         
| source             | character varying(20)  | not null                                                                
| company_id         | integer                |                                                                         
| eyp_list_id        | integer                |                                                                         
| iid_list_id        | integer                |                                                                         
| ip_list_id         | integer                |                                                                         
| catalog_company_id | integer                |                                                                         
| extra_attributes   | boolean                | not null default false                                                  
|
------------------------------------------------------------------------
Indexes: profile_master_profile_id_pkey primary key btree (profile_id),
         profile_master_email btree (email)


-- 
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.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is 292 inserts/sec acceptable performance ?
@ 2003-04-29 07:25  Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 1 reply; 6+ messages in thread

From: Shridhar Daithankar @ 2003-04-29 07:25 UTC (permalink / raw)
  To: 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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is 292 inserts/sec acceptable performance ?
@ 2003-04-29 11:12  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 0 replies; 6+ messages in thread

From: Rajesh Kumar Mallah @ 2003-04-29 11:12 UTC (permalink / raw)
  To: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>; pgsql-performance


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.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is 292 inserts/sec acceptable performance ?
@ 2003-04-29 14:00  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Rajesh Kumar Mallah <mallah@trade-india.com>
  1 sibling, 2 replies; 6+ messages in thread

From: Tom Lane @ 2003-04-29 14:00 UTC (permalink / raw)
  To: Rajesh Kumar Mallah <mallah@trade-india.com>; +Cc: pgsql-performance

Rajesh Kumar Mallah <mallah@trade-india.com> 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




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is 292 inserts/sec acceptable performance ?
@ 2003-04-29 15:48  mallah@trade-india.com
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: mallah@trade-india.com @ 2003-04-29 15:48 UTC (permalink / raw)
  To: tgl@sss.pgh.pa.us; +Cc: pgsql-performance



it really takes that long :(
i can post it 2morrow only when i am office .


regds
mallah

> Rajesh Kumar Mallah <mallah@trade-india.com> 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



-----------------------------------------
Get your free web based email at trade-india.com.
   "India's Leading B2B eMarketplace.!"
http://www.trade-india.com/




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: Is 292 inserts/sec acceptable performance ?
@ 2003-04-30 07:39  Rajesh Kumar Mallah <mallah@trade-india.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 6+ messages in thread

From: Rajesh Kumar Mallah @ 2003-04-30 07:39 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance


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 <mallah@trade-india.com> 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.




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-04-30 07:39 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-29 07:01 Is 292 inserts/sec acceptable performance ? Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-29 07:25 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-04-29 11:12   ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-29 14:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-04-29 15:48   ` mallah@trade-india.com
2003-04-30 07:39   ` Rajesh Kumar Mallah <mallah@trade-india.com>

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