Обсуждение: 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. 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
> -----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.
Dave,<br /> Cheers for the swift reply<br /><br /> Please refer to my comments below<br /><br /> /Jona<br /><br /> DavePage wrote: <blockquote cite="midE7F85A1B5FF8D44C8A1AF6885BC9A0E490E3DA@ratbert.vale-housing.co.uk" type="cite"><prewrap=""> </pre><blockquote type="cite"><pre wrap="">-----Original Message----- From: <a class="moz-txt-link-abbreviated" href="mailto:pgadmin-support-owner@postgresql.org">pgadmin-support-owner@postgresql.org</a> [<a class="moz-txt-link-freetext" href="mailto:pgadmin-support-owner@postgresql.org">mailto:pgadmin-support-owner@postgresql.org</a>]On Behalf Of Jona Sent: 09 June 2005 11:00 To: <a class="moz-txt-link-abbreviated" href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a> 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. </pre></blockquote><pre wrap=""> Do you mean Primary keys? </pre></blockquote> 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?<br /><blockquote cite="midE7F85A1B5FF8D44C8A1AF6885BC9A0E490E3DA@ratbert.vale-housing.co.uk"type="cite"><pre wrap=""> </pre><blockquote type="cite"><prewrap="">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. </pre></blockquote><pre wrap=""> I'm guessing you mean PostgreSQL ? :-) </pre></blockquote> Ye, you got it! ;-)<br /><blockquote cite="midE7F85A1B5FF8D44C8A1AF6885BC9A0E490E3DA@ratbert.vale-housing.co.uk"type="cite"><pre wrap=""> </pre><blockquote type="cite"><prewrap="">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) ?? </pre></blockquote><pre wrap=""> 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. </pre></blockquote> Hmm... don't know if I agree with you here. I for one (as a user ofa great program) would like to know at least that an index is created for the foreign key, primary key and unique constraint.<br/> 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.<br /> Go figure....<br /><blockquotecite="midE7F85A1B5FF8D44C8A1AF6885BC9A0E490E3DA@ratbert.vale-housing.co.uk" type="cite"><pre wrap=""> Regards, Dave. ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend </pre></blockquote><br />
From: Jona [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 indexesDo 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.
Ye, you got it! ;-)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.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.