"Rajesh Kumar Mallah" <mallah.rajesh@xxxxxxxxx> writes:
> Some of our tables have duplicate indexes on same column by different
> index names.
> Should the database server check for the existance of (effectively)
> same index in
> a table before creating a new one.
I'd vote not; I think this would get in the way of people who do know
what they're doing, as much as it would hold the hands of those who
don't. ("Build a database that even a fool can use, and only a fool
would want to use it.")
An example: suppose you mistakenly created a plain index on foo.bar,
when you meant it to be a unique index. You don't want to just drop the
plain index before creating a unique index, because you have live
clients querying the table and their performance would tank with no
index at all. But surely a plain index and a unique index on the same
column are redundant, so a nannyish database should prevent you from
creating the desired index before dropping the unwanted one.
Other scenarios: is an index on X redundant with one on X,Y? Is a hash
index on X redundant if there's also a btree index on X? How about
partial or functional indexes with slightly varying definitions?
There's been some discussion lately about an "index advisor", which
might reasonably provide some advice if it thinks you have redundant
indexes. But I'm not eager to put any sort of enforcement of the point
into the core database.
regards, tom lane
