| From: | Wes <wespvp(at)msg(dot)bt(dot)com> |
|---|---|
| To: | Vivek Khera <vivek(at)khera(dot)org>, pgsql general <pgsql-general(at)postgresql(dot)org> |
| Subject: | Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX |
| Date: | 2008-02-05 00:37:54 |
| Message-ID: | C3CD0C02.710BA%wespvp@msg.bt.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general pgsql-hackers |
On 2/4/08 9:53 AM, "Vivek Khera" <vivek(at)khera(dot)org> wrote:
> what you need to do is compare the relpages from the pg_class table
> for that index before and after.
>
> if you didn't get much disk space back, make sure you have no long
> running transactions that may have kept some older files open.
I can check that next time, but I only reindex about once a year. There
definitely should be no outstanding transactions.
The reason for the huge change in the vacuum time is that the indexes are
scanned in index order instead of disk order. I understand that is fixed in
8.2 or 8.3 (don't recall which I saw it in), but have never gotten
confirmation from anyone on that.
Wes
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Gurjeet Singh | 2008-02-05 00:39:11 | Re: Reload only specific databases from pg_dumpall |
| Previous Message | Tom Lane | 2008-02-05 00:12:17 | Re: encoding does not match server's locale |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Tom Lane | 2008-02-05 00:55:56 | Re: REINDEX on large DB vs. DROP INDEX/CREATE INDEX |
| Previous Message | Tom Lane | 2008-02-05 00:21:38 | Re: TODO item:Allow to_date() and to_timestamp() accept localized month names |