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 doing CREATE 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 ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html