On Sun, 24 Aug 2008 13:37:11 +0200 Alban Hertroys <dalroi@xxxxxxxxxxxxxxxxxxxxxxxxxxxx> 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
