Re: PRIMARY KEY Indexes.

On Mon, 28 Jan 2002, Marc Mitchell wrote:

> pgsqlPostgres automatically creates a unique index to assure data integrity
> (see CREATE INDEX statement).
>
> The online docs at
> http://www.postgresql.org/idocs/index.php?sql-createtable.html state the
> following:
>
> " Postgres automatically creates a unique index to assure data integrity
> (see CREATE INDEX statement). "  ... when PRIMARY KEY is specified within a
> CREATE TABLE statement.
>
> My questions:
>
> 1) Are these indexes at all visible to a tool like PGAdmin?  How are they
> named?

The name is <table>_pkey.  I'd assume they are visible assuming PGAdmin
doesn't hide them and just show the primary key constraint.

> 2) If FORIGN KEY constraints were present on tables, I assume a lookup is
> done at time of INSERT to ensure the referenced value can be found.  Would
> such a lookup and the index used be seen in the EXPLAIN or EXPLAIN VERBOSE
> output of an INSERT?

It should generally use the index.  There are cases due to the saved plan
where the best plan might change during a session and we won't notice that
until the next session.  The explain output won't show the fk lookups as
far as I know, however.