[ADMIN] impact of truncate table on indexes

[Dinesh Bhandary]

Hi All -

We have been having some performance issues with indexes right after
truncating a table. My understanding is truncate removes all the
contents of the table and the space get reclaimed immediately.
In such cases what happens to its indexes. It does not seem like they
are removed, but merely marked for deletion by a vacuum job and it
creates huge bloats and performance problems.

What is the best procedure in scenarios like this? Recreating indexes
seems to solve the problem, but I am trying to find out if there are
other alternatives out there.

Thanks.
Dinesh

[Tom Lane]

Post by Dinesh Bhandary
We have been having some performance issues with indexes right after
truncating a table.

You'd need to be a lot more specific than that if you want useful help.
Also, pgsql-performance is a better list for discussing performance
issues.
http://wiki.postgresql.org/wiki/SlowQueryQuestions

Post by Dinesh Bhandary
My understanding is truncate removes all the
contents of the table and the space get reclaimed immediately.
In such cases what happens to its indexes.

They get rebuilt as empty indexes.

Post by Dinesh Bhandary
It does not seem like they
are removed, but merely marked for deletion by a vacuum job and it
creates huge bloats and performance problems.

On what do you base this clearly-contrary-to-reality conclusion?

regards, tom lane