Thread: Is 292 inserts/sec acceptable 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.
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
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.
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
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/
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.05rows=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.