I try to add index to tables. (please refer to http://sites.google.com/site/yanchengcheok/Home/question.txt) Database is designed in the following graphical view (please refer to http://sites.google.com/site/yanchengcheok/Home/question.png) Here is the setting of my database. All using default except syncrhonous_commit = off. (please refer to http://sites.google.com/site/yanchengcheok/Home/database.PNG) My database size is as follow : SemiconductorInspection=# \timing on Timing is on. SemiconductorInspection=# SELECT count(*) FROM lot; count ------- 2 (1 row) Time: 1.003 ms SemiconductorInspection=# SELECT count(*) FROM unit; count -------- 206363 (1 row) Time: 92.766 ms SemiconductorInspection=# SELECT count(*) FROM measurement; count --------- 1650904 (1 row) Time: 355.161 ms SemiconductorInspection=# I simply run a delete operation : delete from lot where lot_id = 3; It takes TWO hours and never able to return! I even run VACUUM, with options FULL + FREEZE + ANALYZE but it does not help at all. Is there other optimization steps I had missed out? Thanks and Regards Yan Cheng CHEOK --- On Wed, 1/13/10, Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> wrote: > From: Craig Ringer <craig@xxxxxxxxxxxxxxxxxxxxx> > Subject: Re: Extremely Slow Cascade Delete Operation > To: "Yan Cheng Cheok" <yccheok@xxxxxxxxx> > Cc: pgsql-general@xxxxxxxxxxxxxx > Date: Wednesday, January 13, 2010, 7:01 PM > On 13/01/2010 4:09 PM, Yan Cheng > Cheok wrote: > > I have 3 tables - lot, unit and measurement > > > > 1 lot is having relationship to many unit. > > 1 unit is having relationship to many measurement. > > delete cascade is being used among their relationship > > > > > SemiconductorInspection=# delete from lot where lot_id > = 2; > > Are there indexes on fk_lot_id and fk_unit_id ? If not, a > DELETE from lot will cause a seqscan of unit for affected > units, and if any must be deleted each will cause a seqscan > of measurement for affected measurements. That's going to > get ugly fast. > > -- > Craig Ringer > > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
