Thread: 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
Re: Should duplicate indexes on same column and same table be allowed?
From
"Rajesh Kumar Mallah"
Date:
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
>
Re: Should duplicate indexes on same column and same table be allowed?
From
"Rajesh Kumar Mallah"
Date:
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
Re: Should duplicate indexes on same column and same table be allowed?
From
"Rajesh Kumar Mallah"
Date:
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
Re: Should duplicate indexes on same column and same table be allowed?
From
"Rajesh Kumar Mallah"
Date:
> > 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.