Re: Shorthand for foreign key indices

On Mon, 2005-05-09 at 12:40, John D. Burger wrote:
> >> By the way, I presume foreign key indices are used to check for
> >> referential integrity on insert. Can the query planner also use then
> >> somehow?
> >
> > It can use them the same way it can use any other index.
>
> Hmm, I guess I thought that "foreign key indices" were some special
> kind of index. For instance, I thought that Postgresql might
> effectively pre-compute part of the information it needed to do a join
> on the two relevant columns.
>
> I'm guessing now that we're just talking about a regular index on a
> column that happens to have a foreign key reference to another column.
> Is that the case?

Yes. And no.

PostgreSQL, by default, in a normal fk setup, requires a pk or unique
index on the column that IS the foreign key. i.e. the column being
pointed to.

The column that points at that column requires no index. So, in an
initial setup of FK->PK, only the master table has an index. And yes,
it is just a plain old index, although primary key indexes have a few
extra bits in the catalog to identify them as such and do some automagic
joining, they are otherwise plain old indexes.

Note that pgsql does not support indexes across multiple tables, so
there's really no way to have an index that has all the join data needed
in it alread, since it would need data from both tables.