Обсуждение: Should I keep INDEX on the table?

Hello Forum,

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.
The postgres documentation mentions that the user defined INDEX is just
the duplication if the table has PRIMARY Key or UNIQUE key constraints
with same columns.
http://www.postgresql.org/docs/7.4/static/indexes-unique.html

I require suggestion whether to keep the (duplicate?) used defined INDEX
on the table or remove it.
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.
If I remove the INDEX from the table will my client work without
performing external reindexing?

System Info:
SunOS firerunner 5.9 Generic_112233-01 sun4u sparc SUNW,UltraAX-i2
Memory size: 1024 Megabytes

Postgres Version : 7.4.1

Thanks,
Rajan Bhide

Also can somebody tell me the overhead involved in DROPing and CREATing
the indexes?
How often this activity should be performed?

-----Original Message-----
From: Rajan Bhide
Sent: Friday, May 14, 2004 3:27 PM
To: pgsql-novice@postgresql.org; Tom Lane; Bruce Momjian
Subject: [NOVICE] Should I keep INDEX on the table?


Hello Forum,

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. The postgres documentation mentions that the user defined INDEX
is just the duplication if the table has PRIMARY Key or UNIQUE key
constraints with same columns.
http://www.postgresql.org/docs/7.4/static/indexes-unique.html

I require suggestion whether to keep the (duplicate?) used defined INDEX
on the table or remove it. 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. If I remove the INDEX from
the table will my client work without performing external reindexing?

System Info:
SunOS firerunner 5.9 Generic_112233-01 sun4u sparc SUNW,UltraAX-i2
Memory size: 1024 Megabytes

Postgres Version : 7.4.1

Thanks,
Rajan Bhide



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

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