Re: on delete cascade slowing down delete

On Aug 22, 2008, at 9:45 AM, Ivan Sergio Borgonovo wrote:

> 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?

Hard to tell without the results from explain analyse. It depends on
what the planner decides to do, but it's often faster than the things
we come up with to work around the planner. As a rule of thumb, if
you're trying to work around the planner it is likely your problem is
caused by something else.

Without an explain plan everything is just speculation really, the
planner is quite smart and it knows your data. It tends to outsmart
the devs.

>>> delete from p;
>
>> And no, this would most likely be slower.
>
> Why?

Because of the extra reindex step. If you'd replace that with an
analyse of p, then it may be faster. Or it may not.

You seem to misinterpret the use case for REINDEX. Read here:
http://www.postgresql.org/docs/8.3/interactive/sql-reindex.html

Especially note the usage scenarios ;)

> 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.

Maybe you shouldn't try to speculate on solutions before you
ascertained what the problem is? People asked for an EXPLAIN ANALYSE,
we can't really help you without that.
With it we can probably exactly tell you what's causing your problem,
if we still need to once you see the results.

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,48b147ea243482493511527!