Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok <yccheok@xxxxxxxxx>]

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

I try to perform delete operation on single row of lot.
=======================================================
SemiconductorInspection=# select count(*) from lot;
 count
-------
     2
(1 row)


SemiconductorInspection=# select count(*) from unit;
  count
---------
 1151927
(1 row)


SemiconductorInspection=# select count(*) from measurement;
  count
---------
 9215416
(1 row)


SemiconductorInspection=# VACUUM ANALYZE;
VACUUM

SemiconductorInspection=# delete from lot where lot_id = 2;

Opps, this is a coffee operation. That's mean I can go out to have few cups of coffee and the operation still on going.

Even I use :
SemiconductorInspection=# EXPLAIN ANALYZE delete from lot where lot_id = 2;

It still hang there :(

Any suggestion? Anything I am doing wrong? Or this is the expected performance?

The table SQL is as follow :

   IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 'lot') THEN
        CREATE TABLE lot
        (
          lot_id bigserial NOT NULL,
          CONSTRAINT pk_lot_id PRIMARY KEY (lot_id)
        );    
    END IF;
    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 'unit') THEN
        CREATE TABLE unit
        (
          unit_id bigserial NOT NULL,
          fk_lot_id bigint NOT NULL,
          CONSTRAINT pk_unit_id PRIMARY KEY (unit_id),
          CONSTRAINT fk_lot_id FOREIGN KEY (fk_lot_id)
              REFERENCES lot (lot_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        );    
    END IF;
    IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 'measurement') THEN
        CREATE TABLE measurement
        (
          measurement_id bigserial NOT NULL,
          fk_unit_id bigint NOT NULL,
          CONSTRAINT pk_measurement_id PRIMARY KEY (measurement_id),
          CONSTRAINT fk_unit_id FOREIGN KEY (fk_unit_id)
              REFERENCES unit (unit_id) MATCH SIMPLE
              ON UPDATE NO ACTION ON DELETE CASCADE
        );    
    END IF;    

Thanks and Regards
Yan Cheng CHEOK


      


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general