Re: Temp tables, indexes and DELETE vs. TRUNCATE

Jeff Boes wrote:
> [Apologies if you have seen this before. I just discovered that posting
> to the group via NNTP, at Teranews.com, apparently isn't working since
> my posts aren't showing up in the mailing list archives.]
>
>
> Summary: CREATE INDEX gets slower the more INSERTs you have done on a
> table. VACUUM doesn't appear to fix this, but TRUNCATE TABLE does.

The CREATE INDEX will have to ignore dead tuples left by the DELETE.
TRUNCATE TABLE truncates the underlying relation and rebuilds the
indexes. That's why CREATE INDEX takes an incrementally longer time
over each pass.

> And so on. Adding a VACUUM step, then a VACUUM FULL step, had no effect
> on this creeping slowdown. Eventually, I replaced the DELETE with a
> TRUNCATE TABLE, and found that the CREATE INDEX was much more
> consistent.

Your pseudo-code did:

LOOP:
INSERT DATA
CREATE INDEX
DROP INDEX
DELETE FROM foo;

Where was the VACUUM/VACUUM FULL added in this? A VACUUM added after
the DELETE should be sufficient to allow the dead tuple-space to be
reused. A VACUUM FULL added after the DELETE should result in
basically the same on-disk structure as TRUNCATE TABLE, albeit through
a slower process.

Mike Mascari
mascarm(at)mascari(dot)com