Re: impact of truncate table on indexes

От:

Tom Lane <tgl@sss.pgh.pa.us>

Дата:

22 декабря 2011 г. в 14:40:43

Dinesh Bhandary  writes:
> 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

> 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.

> 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

impact of truncate table on indexes

От:

Dinesh Bhandary <dbhandary@iii.com>

Дата:

22 декабря 2011 г. в 14:06:18

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