Обсуждение: Should duplicate indexes on same column and same table be allowed?

Hi,

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.

Regds
Mallah.

"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

On 12/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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.

I meant *exactly* the same index (pls ignore the word effectively in prv  post).
even same tablespace.

Regds
mallah.

PS: (forgive me for my meager knowledge of internals)



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

Sir,

Suppose an index get corrupted. And you need create a new index
with exact specs and then drop the old index. Is it better to
have a performing corrupted index or not have it at all and temporarily
suffer some performance degradation ?

that was one scenerio which comes to my mind for having duplicate indexes.


Regds
mallah.

On 12/9/06, Rajesh Kumar Mallah <mallah.rajesh@gmail.com> wrote:
> On 12/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "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.
>
> I meant *exactly* the same index (pls ignore the word effectively in prv  post).
> even same tablespace.
>
> Regds
> mallah.
>
> PS: (forgive me for my meager knowledge of internals)
>
>
>
> >
> > 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
> >
>

"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> Suppose an index get corrupted. And you need create a new index
> with exact specs and then drop the old index. Is it better to
> have a performing corrupted index or not have it at all and temporarily
> suffer some performance degradation ?

The case that was being discussed just a day or two ago was where you
wanted to do the equivalent of REINDEX because of index bloat, not any
functional "corruption".  In that case it's perfectly clear that
temporarily not having the index isn't acceptable ... especially if
it's enforcing a unique constraint.

            regards, tom lane

On 12/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> > Suppose an index get corrupted. And you need create a new index
> > with exact specs and then drop the old index. Is it better to
> > have a performing corrupted index or not have it at all and temporarily
> > suffer some performance degradation ?
>
> The case that was being discussed just a day or two ago was where you
> wanted to do the equivalent of REINDEX because of index bloat, not any
> functional "corruption".  In that case it's perfectly clear that
> temporarily not having the index isn't acceptable ... especially if
> it's enforcing a unique constraint.

Sorry ,
i guess i digressed .
Lemme put the question once again.

psql> CREATE INDEX x on test (col1);
psql> CREATE INDEX y on test (col1);

What is (are) the downsides of disallowing the
second index. which is *exactly* same as
previous?

Regds
mallah.

>
>                         regards, tom lane
>

Em Dom, 2006-12-10 às 00:47 +0530, Rajesh Kumar Mallah escreveu:
> psql> CREATE INDEX x on test (col1);
> psql> CREATE INDEX y on test (col1);
>
> What is (are) the downsides of disallowing the
> second index. which is *exactly* same as
> previous?

What if PostgreSQL make a NOTICE about this? The user could see it and
take some action about it.

Sincerely,
--
Daniel Cristian Cruz
クルズ クリスチアン ダニエル

On Sat, 2006-12-09 at 12:46 -0500, Tom Lane wrote:
> "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> > Suppose an index get corrupted. And you need create a new index
> > with exact specs and then drop the old index. Is it better to
> > have a performing corrupted index or not have it at all and temporarily
> > suffer some performance degradation ?
>
> The case that was being discussed just a day or two ago was where you
> wanted to do the equivalent of REINDEX because of index bloat, not any
> functional "corruption".  In that case it's perfectly clear that
> temporarily not having the index isn't acceptable ... especially if
> it's enforcing a unique constraint.

I can see that is quite handy.

However, I can't see any benefit to allowing multiple FKs:

postgres=# alter table accounts add foreign key (bid) references
branches;
ALTER TABLE
Time: 1678.240 ms
postgres=# alter table accounts add foreign key (bid) references
branches;
ALTER TABLE
Time: 909.706 ms
postgres=# alter table accounts add foreign key (bid) references
branches;
ALTER TABLE
Time: 507.673 ms
postgres=# alter table accounts add foreign key (bid) references
branches;
ALTER TABLE
Time: 597.909 ms
postgres=# alter table accounts add foreign key (bid) references
branches;
ALTER TABLE
Time: 677.125 ms
postgres=# \d accounts
    Table "public.accounts"
  Column  |  Type   | Modifiers
----------+---------+-----------
 aid      | integer | not null
 bid      | integer |
 abalance | integer |
Indexes:
    "accounts_pkey" PRIMARY KEY, btree (aid)
Foreign-key constraints:
    "accounts_bid_fkey" FOREIGN KEY (bid) REFERENCES branches(bid)
    "accounts_bid_fkey1" FOREIGN KEY (bid) REFERENCES branches(bid)
    "accounts_bid_fkey2" FOREIGN KEY (bid) REFERENCES branches(bid)
    "accounts_bid_fkey3" FOREIGN KEY (bid) REFERENCES branches(bid)
    "accounts_bid_fkey4" FOREIGN KEY (bid) REFERENCES branches(bid)


--
  Simon Riggs
  EnterpriseDB   http://www.enterprisedb.com

From time to time, I need to unload rows to a delimited file,
specifically with a "where" clause.
I've cobbled a script together to do this, but it seems like a
reasonable utility to support. Sort
of a pg_dump on steroids..

Have I missed the simple way to do this?  Would someone consider adding
such a utility or adding
this to pg_dump?

Naomi

--
----------------------------------------------------------------------------
Naomi Walker                          Chief Information Officer
Mphasis Healthcare Solutions          nwalker@mhs.mphasis.com
  ---An EDS Company                   602-604-3100
----------------------------------------------------------------------------
A positive attitude may not solve all your problems, but it will annoy
enough people to make it worth the effort. --Herm Albright (1876 - 1944)
----------------------------------------------------------------------------

-- CONFIDENTIALITY NOTICE --

Information transmitted by this e-mail is proprietary to MphasiS and/or its Customers and is intended for use only by
theindividual or entity to which it is addressed, and may contain information that is privileged, confidential or
exemptfrom disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has
beenforwarded to you without proper authority, you are notified that any use or dissemination of this information in
anymanner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this
mailfrom your records. 

Naomi Walker wrote:
> From time to time, I need to unload rows to a delimited file,
> specifically with a "where" clause.
> I've cobbled a script together to do this, but it seems like a
> reasonable utility to support. Sort
> of a pg_dump on steroids..
>
> Have I missed the simple way to do this?  Would someone consider adding
> such a utility or adding
> this to pg_dump?
>
> Naomi

8.2 COPY appears to be able to output in csv format, using a WHERE
clause, though I've not tried it.

http://www.postgresql.org/docs/8.2/static/sql-copy.html

Rajesh Kumar Mallah wrote:
> On 12/9/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> > > Suppose an index get corrupted. And you need create a new index
> > > with exact specs and then drop the old index. Is it better to
> > > have a performing corrupted index or not have it at all and temporarily
> > > suffer some performance degradation ?
> >
> > The case that was being discussed just a day or two ago was where you
> > wanted to do the equivalent of REINDEX because of index bloat, not any
> > functional "corruption".  In that case it's perfectly clear that
> > temporarily not having the index isn't acceptable ... especially if
> > it's enforcing a unique constraint.
>
> Sorry ,
> i guess i digressed .
> Lemme put the question once again.
>
> psql> CREATE INDEX x on test (col1);
> psql> CREATE INDEX y on test (col1);
>
> What is (are) the downsides of disallowing the
> second index. which is *exactly* same as
> previous?

The cost of preventing every stupid database use is too high.

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

> > Sorry ,
> > i guess i digressed .
> > Lemme put the question once again.
> >
> > psql> CREATE INDEX x on test (col1);
> > psql> CREATE INDEX y on test (col1);
> >
> > What is (are) the downsides of disallowing the
> > second index. which is *exactly* same as
> > previous?
>
> The cost of preventing every stupid database use is too high.

Although a notice that says:

NOTICE: CREATE DUPLICATE INDEX y on test (col1);

Probably isn't out of order.

Sincerely,

Joshua D. Drake

>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

"Joshua D. Drake" <jd@commandprompt.com> writes:
>> The cost of preventing every stupid database use is too high.

> Although a notice that says:
> NOTICE: CREATE DUPLICATE INDEX y on test (col1);
> Probably isn't out of order.

You're omitting the (probably) several pages of C code that would be
needed to detect whether the index is really a duplicate or not.

            regards, tom lane

On Wed, 2006-12-13 at 18:55 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> >> The cost of preventing every stupid database use is too high.
>
> > Although a notice that says:
> > NOTICE: CREATE DUPLICATE INDEX y on test (col1);
> > Probably isn't out of order.
>
> You're omitting the (probably) several pages of C code that would be
> needed to detect whether the index is really a duplicate or not.

Bah, hmmm maybe a perl script in contrib :)

Sincerely,

Joshua D. Drake


>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

>
> The cost of preventing every stupid database use is too high.
>
> --
thanks it answers my concern. many a times we face the same
situation with the marketing people of our company.  the complexity
of stopping stupid usage can be  quite non trivial at times.

regds
mallah.