VACUUM/VACUUM FULL/REINDEX
VACUUM/VACUUM FULL/REINDEX
От:
Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
Дата:
I'm a little confused about comments I've seen on this list - a VACUUM is not enough, clearly, to maintain a trim, efficient DB. People have mentioned that REINDEX is also needed periodically. Is this the case even if one VACUUM FULL's periodically, or does that effectively REINDEX also? -- Sam Barnett-Cormack Software Developer | Student of Physics & Maths UK Mirror Service (http://www.mirror.ac.uk) | Lancaster University
Re: VACUUM/VACUUM FULL/REINDEX
От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Sam Barnett-Cormack writes: > I'm a little confused about comments I've seen on this list - a VACUUM > is not enough, clearly, to maintain a trim, efficient DB. People have > mentioned that REINDEX is also needed periodically. If you have an index whose range of values continually shifts (for example, a timestamp or serial-number column) then you will probably need to REINDEX it every so often to reclaim empty space in the index. This is at least partially fixed for 7.4, but it's necessary in all prior versions. The problem is that VACUUM had no mechanism to reclaim btree pages that become totally empty because there's no longer any data in the range of key values they represent. When the indexed column's statistics aren't changing much over time, I wouldn't think you'd need to do routine reindexing --- just keeping after the table with VACUUM ought to be enough. regards, tom lane