pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
impact of truncate table on indexes 2+ messages / 2 participants [nested] [flat]
* impact of truncate table on indexes @ 2011-12-22 18:06 Dinesh Bhandary <dbhandary@iii.com> 0 siblings, 1 reply; 2+ messages in thread From: Dinesh Bhandary @ 2011-12-22 18:06 UTC (permalink / raw) To: pgsql-admin 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: impact of truncate table on indexes @ 2011-12-22 18:40 Tom Lane <tgl@sss.pgh.pa.us> parent: Dinesh Bhandary <dbhandary@iii.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2011-12-22 18:40 UTC (permalink / raw) To: dbhandary@iii.com; +Cc: pgsql-admin Dinesh Bhandary <dbhandary@iii.com> 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2011-12-22 18:40 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2011-12-22 18:06 impact of truncate table on indexes Dinesh Bhandary <dbhandary@iii.com> 2011-12-22 18:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox