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

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

From
"Rajan Bhide"
Date:
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
reindexingas 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
shouldbe performed? 
This is really URGENT guys.. So do give your feedbacks.

Thanks,
Rajan Bhide


-----Original Message-----
From: Oliver Fromme [mailto:olli@lurza.secnetix.de]
Sent: Friday, May 14, 2004 6:16 PM
To: Rajan Bhide
Cc: pgsql-novice@postgresql.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
haschanged 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
performexternal reindexing  > (DROP and CREATE INDEX) every 2 Hours to reclaim the dead space left  > behind by the
deletedrows. 

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
personalto 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


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

From
Oliver Fromme
Date:
Rajan Bhide wrote:
 > 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.

I can't believe that's true.  That would be a serious show-
stopper bug in PostgreSQL, in my opinion.  The documentation
does not mention anywhere that periodically dropping and re-
creating indices might be necessary.  In fact, in chapter
11.8 it specifically says "indexes in PostgreSQL do not need
maintenance and tuning".

(Can anyone of the developers confirm or deny that?)

 > http://archives.postgresql.org/pgsql-general/2002-06/msg00813.php

That article is two years old, and describes different symp-
toms than what you described.  So I think it does not apply
to your case.

 > Also Can Some one help me understand the overhead involved
 > in DROPing and CREATing the indexes? How often this
 > activity should be performed?

I seriously hope it should not have be performed at all.
If you run a database cluster with dozens or even hundreds
of customers on it, it appears pretty ridculous to me to
have to rebuild all of their indices every night.  I don't
even want to know what indices they have -- it's the job
of the database to maintain that and care for it.

Frankly, I think VACUUMimg the databases (possibly with the
"FULL" option) should be enough for normal maintenance.
If it's not, I would consider that a bug, or at least a
serious PITA that deservers to be removed ASAP.  ;-)

(Just my 0.02 Euro.)

Best regards
   Oliver

--
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.

"That's what I love about GUIs: They make simple tasks easier,
and complex tasks impossible."
        -- John William Chambless

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

From
Bruno Wolff III
Date:
On Mon, May 17, 2004 at 10:09:32 +0200,
  Oliver Fromme <olli@lurza.secnetix.de> wrote:
>
> I can't believe that's true.  That would be a serious show-
> stopper bug in PostgreSQL, in my opinion.  The documentation
> does not mention anywhere that periodically dropping and re-
> creating indices might be necessary.  In fact, in chapter
> 11.8 it specifically says "indexes in PostgreSQL do not need
> maintenance and tuning".

There was a problem with reclaming dead space in btrees before 7.4.
If old data was deleted and new data added with the key for the new
date always increasing (or always decreasing), then the freed up
pages would never get reused. Changes were mage in 7.4 to alleviate
this problem. The developers weren't sure if there were still some
usage patterns that could result in a lot of wasted space. My memory
is that there was an O(n) upper bound on the space used now, but I
might be wrong about that.