From: | Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> |
---|---|
To: | Yan Cheng Cheok <yccheok(at)yahoo(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Extremely Slow Cascade Delete Operation |
Date: | 2010-01-14 05:55:42 |
Message-ID: | 4B4EB1DE.8000106@postnewspapers.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-01-14 05:58:56 | Re: Collate order on Mac OS X, text with diacritics in UTF-8 |
Previous Message | Craig Ringer | 2010-01-14 05:32:04 | Re: Collate order on Mac OS X, text with diacritics in UTF-8 |