Pgadmin - Foreing Key / Unique Contraint indexes

Author

Foreing Key / Unique Contraint indexes

Jona

2005-06-09, 7:23 am

Hey
I'm unable to see the indexes for foreign keys and unique constraints on
my tables, all other indexes are showing up fine in the table tree.

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.

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

Your input is very much appreciated.

Cheers
Jona

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

Dave Page

2005-06-09, 7:23 am

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Jona
> Sent: 09 June 2005 11:00
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] Foreing Key / Unique Contraint indexes
>
> Hey
> I'm unable to see the indexes for foreign keys and unique
> constraints on
> my tables, all other indexes are showing up fine in the table tree.

Do you mean Primary keys?

> 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 ? :-)

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

Regards, Dave.

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

Jona

2005-06-09, 9:24 am

Dave,
Cheers for the swift reply

Please refer to my comments below

/Jona

Dave Page wrote:

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

>
>
>
>I'm guessing you mean PostgreSQL ? :-)
>
>
Ye, you got it! ;-)

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

>Regards, Dave.
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>

Dave Page

2005-06-09, 9:24 am

____________________
____________

From: Jona & #91;mailto:jonanews@
oismail.com]
Sent: 09 June 2005 14:38
To: Dave Page
Cc: pgadmin-support@postgresql.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.

Sponsored Links