Re: Slow Inserts on 1 table?

On Tue, Aug 02, 2005 at 10:55:22AM -0700, Gregory Youngblood wrote:
> Not to fan the flames, so to speak, but I do have on question. If
> this is a known limitation, I'd expect the behavior to be consistent.
> Instead, he is saying that the problem is intermittent. Sometimes it
> runs fine, other times it slows down to a snail's pace.
>
> So, does that mean the known problem is indeed intermittent, and not
> something that happens every time? Or, is this an issue that can be
> mostly eliminated with appropriate tuning?

The problem is basically that PostgreSQL chooses a seq scan if it
thinks the table is empty. But a normal CREATE TABLE fudges the
statistics so that it chooses an index scan.

The problem mostly arises if you use an ANALYZE or VACUUM ANALYZE
*after* you've deleted all the rows but *before* you load the data.
Don't do that. If you need to run VACUUM to clear out the table, do so,
just don't analyze at the same time. Or use TRUNCATE.

At least that's my experience.

Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.