Обсуждение: Is 292 inserts/sec acceptable performance ?

Поиск
Список
Период
Сортировка

Is 292 inserts/sec acceptable performance ?

От
Rajesh Kumar Mallah
Дата:

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.

Re: Is 292 inserts/sec acceptable performance ?

От
Shridhar Daithankar
Дата:
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

Re: Is 292 inserts/sec acceptable performance ?

От
Rajesh Kumar Mallah
Дата:
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.

Re: Is 292 inserts/sec acceptable performance ?

От
Tom Lane
Дата:
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

Re: Is 292 inserts/sec acceptable 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/

Re: Is 292 inserts/sec acceptable performance ?

От
Rajesh Kumar Mallah
Дата:
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.

Чтобы сделать работу с сайтом удобнее, мы используем cookie и аналитический сервис «Яндекс.Метрика». Продолжая пользоваться сайтом, вы соглашаетесь с их использованием.