Обсуждение: Shorthand for foreign key indices
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
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?"
> 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
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?"
>> 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
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.
> 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.
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.