Re: Slow Inserts on 1 table?

> my guess is because analyze has not been run yet, so it thinks all of
> the tables are size 0. If I let it run for a while, then kill the
> load process, run Analyze, empty the tables, and then restart, things
> perform fine. But that is kind of a ridiculous sequence to have to
> use to load a database.

So automate it. After discovering exactly this behavior, I've
developed an idiom for load scripts where I (optionally) commit at some
linear interval, and (optionally) analyze at some exponential interval.
I presume this has been invented countless times, but here's my basic
idea in pseudo-code:

commitInterval = 1000
analyzeFactor = 2
whenToCommit = whenToAnalyze = commitInterval
nInserts = 0

loop over input data
if we decide to insert
insert
nInserts++
if whenToCommit < nInserts
commmit
whenToCommit += commitInterval
if whenToAnalyze < nInserts
analyze
whenToAnalyze *= 2
....

So (with these constants) we commit after 1000 total inserts, then
after 2000, 3000, etc. And we analyze after 1000 inserts, then after
2000, 4000, etc. This is perhaps way too conservative - in particular,
I suspect that it's only the first one or two analyzes that matter -
but it works for me.

The only annoyance is that the interface I use most often, Python's
pgdb, runs everything in a transaction, and you can't analyze in a
transaction. I've gotten around this in a variety of ways, some less
principled than others.

- John D. Burger
MITRE