From: John A Meinel <john@arbash-meinel.com>
To: Yves Vindevogel <yves.vindevogel@implements.be>
Cc: pgsql-performance@postgresql.org
Subject: Re: Another question on indexes (drop and recreate)
Date: Tue, 21 Jun 2005 10:49:28 -0500
Message-ID: <42B83708.2050706@arbash-meinel.com> (raw)
In-Reply-To: <821370985653dc1f4166ca634f642ce2@implements.be>
References: <098952ec5af9b040acb9b6b5af743abd@implements.be>
<42B830B3.6060902@arbash-meinel.com>
<821370985653dc1f4166ca634f642ce2@implements.be>
Yves Vindevogel wrote:
> And, after let's say a week, would that index still be optimal or
> would it be a good idea to drop it in the weekend and recreate it.
It depends a little bit on the postgres version you are using. If you
are only ever adding to the table, and you are not updating it or
deleting from it, I think the index is always optimal.
Once you start deleting from it there are a few cases where older
versions would not properly re-use the empty entries, requiring a
REINDEX. (Deleting low numbers and always adding high numbers was one of
the cases)
However, I believe that as long as you vacuum often enough, so that the
system knows where the unused entries are, you don't ever have to drop
and re-create the index.
John
=:->
Attachments:
signature.asc (application/pgp-signature, 252B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: john@arbash-meinel.com, yves.vindevogel@implements.be
Subject: Re: Another question on indexes (drop and recreate)
In-Reply-To: <42B83708.2050706@arbash-meinel.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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