From: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
---|---|
To: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Extremely Slow Cascade Delete Operation |
Date: | 2010-01-14 05:26:30 |
Message-ID: | 405923.27000.qm@web65711.mail.ac4.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)postnewspapers(dot)com(dot)au> wrote:
> From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" <yccheok(at)yahoo(dot)com>
> Cc: pgsql-general(at)postgresql(dot)org
> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2010-01-14 05:32:04 | Re: Collate order on Mac OS X, text with diacritics in UTF-8 |
Previous Message | Leigh Dyer | 2010-01-14 04:35:07 | Re: Backup strategies with significant bytea data |