Is 292 inserts/sec acceptable performance ? - Mailing list 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.