Re: Performance problems - Indexes and VACUUM

"Josh Berkus" <josh(at)agliodbs(dot)com> writes:
> This is on 7.1.2 (SuSE 7.2, ReiserFS, PG built from source). Explicitly
> dropping the indexes before dropping the tables seems to have solved the
> problem. My guess, without understanding the guts of the thing at all,
> is that the transactional nature of the drop and re-create causes the
> indexes not to be fully cleared before they are re-built. Maybe it's
> even a reaction to the journaling file system.

I don't believe a single word of that explanation ... whatever is going
on here, that ain't it. A new table is going to have a new OID, and
so will its indexes; there is no way that Postgres will confuse it with
the old one, even if bits of the old one were still hanging around
somehow (which I don't believe either).

One thing to think about, if you are dropping and recreating tables in
a plpgsql function, is that you probably need to do it with EXECUTE
to avoid plan caching.

> BTW, any issues with PostgreSQL and DMA disk access?

Not unless your kernel or hardware are broken. But I have seen cases
where hardware problems (eg, bogus DMA controller) manifested themselves
only as database errors. Evidently Postgres was pushing the disk harder
than anything else on the system, so it was more likely to get bit by
a sporadic hardware booboo.

regards, tom lane