Re: on delete cascade slowing down delete

On Sun, 24 Aug 2008 13:37:11 +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 ;)

http://www.postgresql.org/docs/8.1/interactive/routine-reindex.html

So on later version than 7.4... what's going to happen if I delete a
whole table?
It looks like it is not an issue and at least reindexing can be
avoided.

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

As to my understanding EXPLAIN ANALYSE does actually run the
query... but it was so damn slow to have result in a useful time.
I re engineered the tables and now the stuff works at a reasonable
speed.

Does the planner optimise multiple statements in a transaction or
just a statement at a time?

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