Обсуждение: VACUUM and index
Hello List, Sorry to send an other e-mail on this subject but it is become a little bit vague for me. Imagine you have a database on which a lot of transactions are done daily : the tables will contain a lot of dead tuples and the B-tree index too. If I do a VACUUM on this database, will the B-tree index be scanned, space reclaimed and made available for re-use ? Does this action only do by a VACUUM FULL ? Or is it necessary to do a REINDEX on the index to retrieve a well-balanced tree ? Thank you for your help. Regards, Alexandra DANTE
On Wed, Jul 19, 2006 at 03:03:43PM +0200, DANTE Alexandra wrote: > Imagine you have a database on which a lot of transactions are done > daily : the tables will contain a lot of dead tuples and the B-tree > index too. > > If I do a VACUUM on this database, will the B-tree index be scanned, > space reclaimed and made available for re-use ? Old versions didn't, but recent versions do. > Does this action only do by a VACUUM FULL ? Also by VACUUM FULL, but plain VACIIM is enough. > Or is it necessary to do a REINDEX on the index to retrieve a > well-balanced tree ? There are always corner case situations. In older versions a REINDEX helped balance the tree but recent versions are much better. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Вложения
Thank you Martijn for your answer ! I use the 8.1.3 release of PostgreSQL. Will it be sensible to launch an ANALYZE after the VACUUM to update statistics ? Regards, Alexandra Martijn van Oosterhout wrote: >On Wed, Jul 19, 2006 at 03:03:43PM +0200, DANTE Alexandra wrote: > > >>Imagine you have a database on which a lot of transactions are done >>daily : the tables will contain a lot of dead tuples and the B-tree >>index too. >> >>If I do a VACUUM on this database, will the B-tree index be scanned, >>space reclaimed and made available for re-use ? >> >> > >Old versions didn't, but recent versions do. > > > >>Does this action only do by a VACUUM FULL ? >> >> > >Also by VACUUM FULL, but plain VACIIM is enough. > > > >>Or is it necessary to do a REINDEX on the index to retrieve a >>well-balanced tree ? >> >> > >There are always corner case situations. In older versions a REINDEX >helped balance the tree but recent versions are much better. > >Have a nice day, > >
On Wed, Jul 19, 2006 at 03:23:08PM +0200, DANTE Alexandra wrote: > Thank you Martijn for your answer ! > I use the 8.1.3 release of PostgreSQL. You'll be fine... > Will it be sensible to launch an ANALYZE after the VACUUM to update > statistics ? Or do both at the same time (VACUUM ANALYZE). Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.