Re: Foreing Key / Unique Contraint indexes

________________________________

From: Jona [mailto:jonanews(at)oismail(dot)com]
Sent: 09 June 2005 14:38
To: Dave Page
Cc: pgadmin-support(at)postgresql(dot)org
Subject: Re: [pgadmin-support] Foreing Key / Unique Contraint
indexes

Do you mean Primary keys?

Looking at the table tree again it seems that only "true"
indexes are displayed, seem to recall that in a previous version of
pgAdmin Primary keys were shown as well?

Quite possibly.

Am running pgAdmin 3, 1.3.0 from May 20th 2005
up against either a
PostGre 7.3.9 on Linux or a PostGre 8.0.3 on a
Windows 2000 server.

I'm guessing you mean PostgreSQL ? :-)

Ye, you got it! ;-)

Is the issue in pgAdmin (that it doesn't show
the indexes for some
reason) or in PostGreSQL (that it doesn't create
the indexes
eventhough
it says it does) ??

pgAdmin hides indexes that implement other types of
object. The fact
that a unique constraint or a pkey is actually an index
is an
implementation detail and is unimportant to the user, in
the same way
that it is unimportant to show foreign keys as sets of
triggers and
views as rules as they really are.

Hmm... don't know if I agree with you here. I for one (as a user
of a great program) would like to know at least that an index is created
for the foreign key, primary key and unique constraint.

Last time I checked foreign keys were not implemented using indexes,
only triggers. Unique constraints and primary keys are index based.

Especially because I've noticed that PostGreSQL seems to indexes
on foreign keys and unique constraints? But if you create another index
on the same column it suddenly starts using that.
Go figure....

It will use what ever it deems to be the most cost-effective access
method at it's disposal, whether that is a seq scan, the primary
key/unique index or any other index. Whether you think of them as an
index or not is another matter - pgAdmin presents the implementation as
you would have specified it in the SQL - ie. if you create a primary
key, that is what it will show you. If you're working at a level where
you are anaylsing query plans, then it's not unreasonable to assume that
you know enough to realise that pks, etc are just indexes.

Showing them as seperate items requires that novices as well as experts
understand that one object is used to implement the other - otherwise
they just get confused (especially with foreign keys where you start
seeing multiple triggers with trigger functions attached to each).

Regards, Dave.