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.

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

"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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

"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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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
クルズ クリス�アン ダニエル


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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 the individual or entity to which it is addressed, and may contain information that isÂ*privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this e-mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at mailmaster@mphasis.com and delete this mail from your records.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate