Thread: Shorthand for foreign key indices

Shorthand for foreign key indices

From
Brendan Jurd
Date:
Does anybody else think it would be cool if you could use a shorthand
expression for creating an index on a foreign key?  I think it's fair
to say that in the majority of cases, if you're using a foreign key,
you're going to want an index on it.

I know that it was decided a fair few releases ago to stop creating an
implicit index for each foreign key, and that's cool, but should the
creation syntax really require a separate statement for every single
FK you want to index?

Something like ...

CREATE TABLE foo (
 foo int NOT NULL REFERENCES bar INDEX
);

... would be marvellous

My apologies if this has been answered before, but a search of the
-general mailing list was not fruitful.

BJ

Re: Shorthand for foreign key indices

From
"Jim C. Nasby"
Date:
On Mon, May 09, 2005 at 02:05:14AM +1000, Brendan Jurd wrote:
> CREATE TABLE foo (
>  foo int NOT NULL REFERENCES bar INDEX
> );
>
> ... would be marvellous

I agree that it would be handy. Another possibility is throwing a NOTICE
or even WARNING if you create a foreign key that isn't covered by an
index.

> My apologies if this has been answered before, but a search of the
> -general mailing list was not fruitful.

FYI, often times new ideas are only discussed on -hackers, so you should
search there as well.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Shorthand for foreign key indices

From
"John D. Burger"
Date:
> I know that it was decided a fair few releases ago to stop creating an
> implicit index for each foreign key,

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?

Thanks.

- John D. Burger
   MITRE



Re: Shorthand for foreign key indices

From
"Jim C. Nasby"
Date:
On Mon, May 09, 2005 at 09:04:49AM -0400, John D. Burger wrote:
> >I know that it was decided a fair few releases ago to stop creating an
> >implicit index for each foreign key,
>
> 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.
--
Jim C. Nasby, Database Consultant               decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

Re: Shorthand for foreign key indices

From
"John D. Burger"
Date:
>> 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?

Thanks.

- John D. Burger
   MITRE



Re: Shorthand for foreign key indices

From
Scott Marlowe
Date:
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.

Re: Shorthand for foreign key indices

From
Brendan Jurd
Date:
> 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?
>

That's true, but just as the query planner can use an index for WHERE
conditions, it can use an index for a join condition.  So if you've
got an index on your foreign key, the planner has the opportunity to
use the index any join operations across that foreign key.

Re: Shorthand for foreign key indices

From
Brendan Jurd
Date:
On 5/9/05, Jim C. Nasby <decibel@decibel.org> wrote:
> On Mon, May 09, 2005 at 02:05:14AM +1000, Brendan Jurd wrote:
> > CREATE TABLE foo (
> >  foo int NOT NULL REFERENCES bar INDEX
> > );
> >
> > ... would be marvellous
>
> I agree that it would be handy. Another possibility is throwing a NOTICE
> or even WARNING if you create a foreign key that isn't covered by an
> index.
>

Good idea.  WARNING might be going too far though.  NOTICE sounds just
about right to me.

> > My apologies if this has been answered before, but a search of the
> > -general mailing list was not fruitful.
>
> FYI, often times new ideas are only discussed on -hackers, so you should
> search there as well.

Well, so far nobody's raised any objections.

Would it be worthwhile to repost this on -hackers?  I did do a search
on the mailing list archives for that list as well, and didn't come up
with anything that bore directly on this discussion.