| From: | Josh Berkus <josh(at)agliodbs(dot)com> |
|---|---|
| To: | Dror Matalon <dror(at)zapatec(dot)com>, sfpug(at)postgresql(dot)org |
| Subject: | Re: Freebsd vs linux and hardware question |
| Date: | 2003-09-22 18:45:04 |
| Message-ID: | 200309221145.04099.josh@agliodbs.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | sfpug |
Dror,
> VACUUM seems to remove dead tuples in all the indices too. Does REINDEX
> do something else?
VACUUM removes the references to the dead tuples, but does not reclaim the
space. It's like the difference between VACUUM and VACUUM FULL.
In 7.2, you can decrease the frequency of VACUUM FULLs (even down to 1 per
month) by proper adjusting of the Free Space Map. However, this is not
extended to indexes until 7.4. As a result, if 75% of the rows in your
index have been updated, then a substantial portion of the disk space used by
your index is empty. I don't know the exact conversion, and suspect it
varies by the size and nature of the updates.
Once that empty space reaches 2/3 or more of the index file, I've found, the
efficiency of the index drops substantially.
--
Josh Berkus
Aglio Database Solutions
San Francisco
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Dror Matalon | 2003-09-22 19:02:08 | Re: Freebsd vs linux and hardware question |
| Previous Message | Dror Matalon | 2003-09-22 18:32:21 | Re: Freebsd vs linux and hardware question |