On Mar 14, 2005, at 12:13 AM, Tom Lane wrote:
"John Engelhart" <[EMAIL PROTECTED]> writes:Since I'm developing an SQL based application, I routinely "start from
scratch" with a script that deletes all the tables in my database and
rebuilds them. A problem started when I upgraded from 7.4.7 to 8.0.1 in
that the first run after the clean takes an UNUSUALLY long time to complete,
on the order of a few hundred inserts/sec. A normal run takes ~30 seconds.
A from scratch run takes ~15 minutes, with the next run completing in 30
seconds with zero changes.
The 8.0 planner is intentionally sensitive to the current actual physical sizes of tables. It sounds like you've managed to get it to plan something on the assumption that the tables are tiny and keep using that plan after they aren't tiny any more. The old planner had the same kind of issue but it was far easier to hit, so "revert that change" isn't an answer that I'm particularly interested in.
I found another data point yesterday. It seems to be session related. I tried various combinations of "after X number of statements, COMMIT, ANALYZE, CHECKPOINT" and none of them helped. The one that did help is after X number of statements, close the database handle and re-open it. So, my program run does about 60K SQL statements, inserting about 22K records. After 500 statements, I close the handle and re-open it. Completes in 30 seconds.
Hopefully this is enough info to track down and recreate the problem
Not really.
Let me know if there's anything that you need.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
