Re: !!URGENT!! Should I keep INDEX on the table?

Hi Oliver,

Thanks, for your reply.
Even I was thinking, VACCUM ANALYSE <table-name> is jes enough but following discussion on forum forced me to add reindexing as I also saw similar error msgs from my postgres server.
>>Correct. Someday, someone will step up to the plate and fix the problem
>>with btrees growing and growing and not reusing dead space.
>>Til then the solution is to reindex heavily updated indexes during nightly
>>maintenance.
http://archives.postgresql.org/pgsql-general/2002-06/msg00813.php

Also Can Some one help me understand the overhead involved in DROPing and CREATing the indexes? How often this activity should be performed?
This is really URGENT guys.. So do give your feedbacks.

Thanks,
Rajan Bhide

-----Original Message-----
From: Oliver Fromme [mailto:olli(at)lurza(dot)secnetix(dot)de]
Sent: Friday, May 14, 2004 6:16 PM
To: Rajan Bhide
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Should I keep INDEX on the table?

Hello,

Rajan Bhide wrote:
> I am having a table with UNIQUE constraints defined on three columns.
> Also I have UNIQUE user defined INDEX with the same columns on the > table.

I think that is redundant. You can easily verify that by dropping the index and then check with EXPLAIN if anything has changed with regards to index usage.

> The problem on removing the index is that my table has almost 2M > transtions in approx ~4 Hours and I need to perform external reindexing > (DROP and CREATE INDEX) every 2 Hours to reclaim the dead space left > behind by the deleted rows.

Uhm, maybe I'm completely misunderstanding you here, but
I think that's the job of VACUUM. There should not be a
need to drop and re-create the index for that purpose.

Best regards
Oliver Fromme

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München Any opinions expressed in this message may be personal to the author and may not necessarily reflect the opinions of secnetix in any way.

"In My Egoistical Opinion, most people's C programs should be indented six feet downward and covered with dirt."
-- Blair P. Houghton