Re: Extremely Slow Cascade Delete Operation

I try to create a following simple scenario, to demonstrate cascade delete is rather slow in PostgreSQL.

Can anyone help me to confirm? Is this my only machine problem, or every PostgreSQL users problem?

I create 1 lot.
every lot is having 10000 unit
every unit is having 100 measurement.

hence :

lot - 1 row entry
unit - 10000 row entries
measurement - 1000000 row entries

run command :

delete from lot where lot_id = 1;

Opps. Never ending....

To reproduce :

(1) Download SQL script from http://sites.google.com/site/yanchengcheok/Home/test.sql

(2) Create a empty database named Sandbox. Follow all default parameters in pgAdmin.

(3) Execute SQL statement in test.sql. It will create tables, inserting data. It may take up to few minutes.

(4) run
delete from lot where lot_id = 1;

dang! dang! dang! a never ending story.

Do you guys get an extremely slow experience as me? Does other database (like MySQL) experience same issues too?

Thanks and Regards
Yan Cheng CHEOK

--- On Wed, 1/13/10, Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com> wrote:

> From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
> 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, 4:35 PM
> It doesn't look like it is locked, so
> it is carrying the delete out.
> However that doesn't mean, that there isn't any other
> locking
> occurring, or simply your disks are rather busy.
>
> Also, maybe the DB is rather big, what are the table sizes
> ?
> If you are using 8.4+, than do \dt+ to get an idea,
> otherwise SELECT
> pg_size_pretty(pg_total_relation_size('table_name')); for
> each table.
>
> --
> 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
>