Skip site navigation (1) Skip section navigation (2)

Should I keep INDEX on the table?

From: "Rajan Bhide" <rbhide(at)nulinkinc(dot)com>
To: <pgsql-novice(at)postgresql(dot)org>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>," Bruce Momjian" <pgman(at)candle(dot)pha(dot)pa(dot)us>
Subject: Should I keep INDEX on the table?
Date: 2004-05-14 09:57:18
Message-ID: FF851C7EEB75954F9BCFB5CA117AB1ECFC321C@delta.nulinkinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
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



Responses

pgsql-novice by date

Next:From: Rajan BhideDate: 2004-05-14 11:26:39
Subject: Re: Should I keep INDEX on the table?
Previous:From: Matthias LenzDate: 2004-05-13 11:10:08
Subject: Re: Changing a relation's name in parser stage

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group