Re: on delete cascade slowing down delete - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject Re: on delete cascade slowing down delete
Date
Msg-id 20080822094542.72fcbdb7@dawn.webthatworks.it
Whole thread Raw
In response to Re: on delete cascade slowing down delete  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
Responses Re: on delete cascade slowing down delete  (Alban Hertroys <dalroi@solfertje.student.utwente.nl>)
List pgsql-general
On Fri, 22 Aug 2008 08:48:30 +0200
Alban Hertroys <dalroi@solfertje.student.utwente.nl> wrote:

>
> > Is it going to make things faster if I:
> >
> > delete from s;
> > reindex table s;

> Why do you think this step would help you any? There's no index on
> p to begin with. You'd just be reindexing the auto-generated
> unique index on s (due to it being a PK).

Sorry I forgot to add the index in the example.
What if there was an index in s.pid too?
But mostly... if I delete s will the deletion of p be faster?

> > delete from p;

> And no, this would most likely be slower.

Why?

Stopping a
delete from p;
I can see that actually postgresql is also executing a
delete from s where pid=$1;
if s is already empty, and there are no other cascading delete on s,
the lookup should be faster. I was wondering if that doesn't make a
difference in terms of performance if
a) I've an index on pid on both tables
or
b) s is already empty

and... should I reindex s if I "delete from s" first if I want some
speed up on delete from p;

Anyway this looks more and more a dead end once things get more and
more complicated since it requires too much bookkeeping.

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


pgsql-general by date:

Previous
From: "andy petrella"
Date:
Subject: hi 5 mess...
Next
From: Ivan Sergio Borgonovo
Date:
Subject: seq bug 2073 and time machine