Peter Bauer wrote:
> Hi all,
>
> for further investigation we seperated the sub-SELECT from the DELETE
> statement and it looks like the SELECT is usually finished in some 100
> milliseconds but after some minutes it suddenly takes some minutes.
Explain analyzes before and after should give some insight into what's
happening. It looks like the query plan changes after some minutes.
What I think is happening (but w/o any proof I'm just guessing) is that
the planner starts with statistics from a relatively empty table (or
something similar statistics-wise) and never gets the opportunity to
update its statistics with information about the newly inserted data. If
that's the case, calling ANALYSE regularly should show improvement (I'm
not sure if autovacuum also analyses - but if so, not frequently enough).
It may also be that the statistics do not match the number of records
and the data you have. Playing with the statistics size may show
improvement.
A quick question for the experts: Is the statistics size equivalent to a
"sample" as known in statistical analysis? If so, there are mathematics
to calculate the required sample size that should at least give people
some idea what size to set it to.
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //