Re: BUG #1541: Unusually long INSERT times after fresh clean/CREATE TABLES

On Mar 14, 2005, at 12:13 AM, Tom Lane wrote:

> "John Engelhart" <johne(at)zang(dot)com> 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
>