Re: on delete cascade slowing down delete

On Thu, 21 Aug 2008 10:19:44 -0700
Joshua Drake <jd(at)commandprompt(dot)com> wrote:

> On Thu, 21 Aug 2008 10:16:21 -0700
> Alan Hodgson <ahodgson(at)simkin(dot)ca> wrote:
>
>
> > > Any hint to track down the problem?

> > 1) Define slow.

still undefined anyway 20-30min compared to over 2h and still
running.

> > 2) Tell us what your disk subsystem consists of.

RAID5 on SCSI, 4Gb RAM, 2xXeon (single core, HT, 3.2GHz)

> > 3) Are there any delete triggers on the tables?

yes as in the subject... several but on very small tables (no more
than some hundreds of record, but mostly in the order of some tens).

BTW work_mem is 32Mb

> And the EXPLAIN ANALYZE

isn't explain analyze going to take forever since it actually run
the statements?

btw if I have

create table p(
pid int primary key
);

create table s(
sid int primary key,
pid int references p (pid) on delete cascade
);

Is it going to make things faster if I:

delete from s;
reindex table s;
delete from p;

or once everything (eg correct indexes that anyway will be useful
for normal operation, postgres.conf suited for my workload and box,
something I still have to discover, etc...) else that don't require
bookkeeping on my part it won't be different from a simpler

delete from p;

?

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it