Triggers taking much time to insert data - Mailing list pgsql-admin

From shreedhar
Subject Triggers taking much time to insert data
Date
Msg-id 000901c2c089$9f450b70$1201a8c0@a4005
Whole thread Raw
List pgsql-admin
Hi All,

I have table with almost 3,500 records with 9 fields which is similar
structure to Table1. From that I am porting to following structure

Table 1.

T1f1 integer serial (primary key),
T1f2 varchar(100) (unique),
T1f3 integer default 0,
T1f4 text,
T1f5 boolean default 'f',
T1f6 boolean default 'f',
T1f7 boolean default 'f',
T1f8 boolean default 'f',
T1f9 integer

In my new database I inserted some more tables which referes to Table1

Table2

T2f1 integer references X1,
T2f2 integer references X2,
T2f3 integer references Table1
primary key (T2f2,T2f3)

So, I wrote a Trigger on Table1 to insert records in Table2 (max. five
insertions will occur)
Where X1 is will grow atleast 25 records with each insert  in Table1 and X2
is having 10 records,

Table3

T3f1 integer references X1,
T3f2 integer references X3,
T3f3 integer references Table1
primary key (T3f2,T3f3)

So, same above Trigger on Table1  insert records in Table3 (max. 20
insertions will occur)
Where X3 is having 70 records. Along with this I am Searching Table3 on two
keys T3f2,T3f3 for existance before inserting.
I am Table3 before inserting for duplication.

Table4
T4f1 integer references X1,
T4f2 integer
primary key (T4f1,T4f2)

So, I wrote  Insert Trigger for Table2 and Table3 to insert in Table4
Table2 Trigger inserts atleast 5 records in Table4.
Table3 Trigger inserts atleast 25 records in Table4.

Table5
T5f1 integer
T5f2 integer
T5f3 integer
T5f4 integer
T5f5 boolean
T5f6 boolean
T5f6 boolean
T5f7 boolean
T5f8 boolean

So, I have a Trigger on Table4 to insert into Table5,  So to insert a record
in Table5 i am doing max (n-1) searches in Table4
atleast maximum of 6 insertions occur.

So to insert a single record in Table1 initially it took 3 seconds directly
from console, if i was running from php program it was taking 6 seconds,
As Data growing in each table insertions will became more complex. It is
taking almost 20seconds to insert a single record. I am porting data similar
structure of Table1 to this new database. In total 6 hours i could not able
to port more than 1500 records
I have  porting program in php.

Hope I had given complete information.

How can I slove this problem.

Please some body give solution.

Thanks & Regards,
Sreedhar


"Faith, faith, faith in ourselves, faith, faith in God, this is the secret
of greatness.
If you have faith in all the three hundred and thirty millions of your
mythological gods,
and in all the gods which foreigners have now and again introduced into your
midst,
and still have no faith in yourselves, there is no salvation for you. "
(III. 190)


pgsql-admin by date:

Previous
From: "Rajesh Kumar Mallah."
Date:
Subject: Re: deadlock problem in Ad serving..
Next
From: Tom Lane
Date:
Subject: Re: deadlock problem in Ad serving..