From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
Cc: Lista Postgres <pgsql-admin@postgresql.org>
Subject: Re: Should duplicate indexes on same column and same table be allowed?
Date: Sat, 09 Dec 2006 01:56:11 -0500
Message-ID: <8397.1165647371@sss.pgh.pa.us> (raw)
In-Reply-To: <a97c77030612082203j5b1dac19i96ef9932d60f375e@mail.gmail.com>
References: <a97c77030612082203j5b1dac19i96ef9932d60f375e@mail.gmail.com>
"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mallah.rajesh@gmail.com
Subject: Re: Should duplicate indexes on same column and same table be allowed?
In-Reply-To: <8397.1165647371@sss.pgh.pa.us>
References: <a97c77030612082203j5b1dac19i96ef9932d60f375e@mail.gmail.com> <8397.1165647371@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox