While reviewing Fabien Coelho's patch for emitting warnings for slow
foreign-key checks, it occurred to me that we aren't covering all bases.
The patch as committed makes sure that there is a usable index on the
referenced table, but it does not look for one on the referencing table.
Failure to provide such an index will lead to slow DELETEs on the
referenced table. And that's a mistake plenty of people make, even
without bringing datatype incompatibilities into it.
I am tempted to add some more code that issues a WARNING about slow
deletes if there's no matching index on the referencing table, or
if that index has type-compatibility problems. (It turns out that
this is not necessarily the same check as whether the PK index has
compatibility problems.)
The main problem with doing this is that in the common case of doingCREATE TABLE foo (f1 int REFERENCES bar);
there will normally not be any matching index available yet. Unless
you want a UNIQUE index, which you often wouldn't, there isn't any
way to make the required index during CREATE TABLE; you have to add
it later. So I'm worried that adding such a warning would create
useless noise during CREATE TABLE.
A possible compromise is to issue warnings only during ALTER TABLE ADD
CONSTRAINT. I'm not sure how useful that would really be though.
Comments, ideas?
regards, tom lane