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