Re: Extremely Slow Cascade Delete Operation

Yan Cheng Cheok wrote:

> I simply run a delete operation :
>
> delete from lot where lot_id = 3;
>
> It takes TWO hours and never able to return!

What does:

EXPLAIN DELETE FROM lot WHERE lot_id = 3;

report?

By the way, you've created a LOT of indexes. Indexes speed up lookups,
but can slow down insert/update/delete. They also use disk space. So
avoid creating indexes for things unless you know the index will
actually be used and be useful. Using EXPLAIN and EXPLAIN ANALYZE are
helpful for discovering this.

In general, I find creating indexes on foreign key columns to be a good
idea unless you never expect to DELETE from the parent table (say, if
you only TRUNCATE, if you rely on partitioning, or if the parent table
is append-only).

--
Craig Ringer