Re: on delete cascade slowing down delete

On Fri, 22 Aug 2008 08:48:30 +0200
Alban Hertroys <dalroi(at)solfertje(dot)student(dot)utwente(dot)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