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@xxxxxxxxxxxxxxxxx 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 //
