pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedShould duplicate indexes on same column and same table be allowed?
13+ messages / 6 participants
[nested] [flat]
* Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 06:03 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-09 06:03 UTC (permalink / raw)
To: pgsql-admin
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 06:56 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-12-09 06:56 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 07:07 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-09 07:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 07:20 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-09 07:20 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
> >
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 17:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
0 siblings, 2 replies; 13+ messages in thread
From: Tom Lane @ 2006-12-09 17:46 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-09 19:17 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 2 replies; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-09 19:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table
@ 2006-12-09 19:45 Daniel Cristian Cruz <danielcristian@gmail.com>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Daniel Cristian Cruz @ 2006-12-09 19:45 UTC (permalink / raw)
To: pgsql-admin
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
クルズ クリスチアン ダニエル
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same tablebe allowed?
@ 2006-12-11 19:30 Simon Riggs <simon@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 13+ messages in thread
From: Simon Riggs @ 2006-12-11 19:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same
@ 2006-12-13 23:49 Bruce Momjian <bruce@momjian.us>
parent: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
1 sibling, 2 replies; 13+ messages in thread
From: Bruce Momjian @ 2006-12-13 23:49 UTC (permalink / raw)
To: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
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. +
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same
@ 2006-12-13 23:54 Joshua D. Drake <jd@commandprompt.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 1 reply; 13+ messages in thread
From: Joshua D. Drake @ 2006-12-13 23:54 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
> > 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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same
@ 2006-12-13 23:55 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joshua D. Drake <jd@commandprompt.com>
0 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2006-12-13 23:55 UTC (permalink / raw)
To: Joshua D. Drake <jd@commandprompt.com>; +Cc: Bruce Momjian <bruce@momjian.us>; Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; pgsql-admin
"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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same
@ 2006-12-13 23:59 Joshua D. Drake <jd@commandprompt.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Joshua D. Drake @ 2006-12-13 23:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Bruce Momjian <bruce@momjian.us>; Rajesh Kumar Mallah <mallah.rajesh@gmail.com>; pgsql-admin
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Should duplicate indexes on same column and same table be allowed?
@ 2006-12-14 02:24 Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
parent: Bruce Momjian <bruce@momjian.us>
1 sibling, 0 replies; 13+ messages in thread
From: Rajesh Kumar Mallah @ 2006-12-14 02:24 UTC (permalink / raw)
To: Bruce Momjian <bruce@momjian.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
>
> 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.
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2006-12-14 02:24 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-12-09 06:03 Should duplicate indexes on same column and same table be allowed? Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-09 06:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-09 07:07 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-09 07:20 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-09 17:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-12-09 19:17 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-09 19:45 ` Re: Should duplicate indexes on same column and same table Daniel Cristian Cruz <danielcristian@gmail.com>
2006-12-13 23:49 ` Re: Should duplicate indexes on same column and same Bruce Momjian <bruce@momjian.us>
2006-12-13 23:54 ` Re: Should duplicate indexes on same column and same Joshua D. Drake <jd@commandprompt.com>
2006-12-13 23:55 ` Re: Should duplicate indexes on same column and same Tom Lane <tgl@sss.pgh.pa.us>
2006-12-13 23:59 ` Re: Should duplicate indexes on same column and same Joshua D. Drake <jd@commandprompt.com>
2006-12-14 02:24 ` Rajesh Kumar Mallah <mallah.rajesh@gmail.com>
2006-12-11 19:30 ` Re: Should duplicate indexes on same column and same tablebe allowed? Simon Riggs <simon@2ndquadrant.com>
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