Re: Extremely Slow Cascade Delete Operation

Поиск
Список
Период
Сортировка
От Yan Cheng Cheok
Тема Re: Extremely Slow Cascade Delete Operation
Дата
Msg-id 534870.35029.qm@web65714.mail.ac4.yahoo.com
обсуждение исходный текст
Ответ на Re: Extremely Slow Cascade Delete Operation  (Grzegorz Jaśkiewicz <gryzman@gmail.com>)
Ответы Re: Extremely Slow Cascade Delete Operation
Список pgsql-general
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@gmail.com> wrote:

> From: Grzegorz Jaśkiewicz <gryzman@gmail.com>
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" <yccheok@yahoo.com>
> Cc: pgsql-general@postgresql.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>





В списке pgsql-general по дате отправления:

Предыдущее
От: Adrian Klaver
Дата:
Сообщение: Re: void stored procedure does return something?
Следующее
От: Alex -
Дата:
Сообщение: Slow Query / Check Point Segments