Re: Further thoughts about warning for costly FK checks

Tom Lane wrote:
> 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.

Yes, I was worried about this too, and mentioned it in relation to the
pg_statistic bucket size discussion we had.

Agreed, there seems to be no good way to emit the warning during table
creation.

Isn't the ALTER TABLE ADD CONSTRAINT used by pg_dump?

Looking at what we have, we know every table will get some inserts, and
we know every insert will have to check the primary key. What we don't
know is if there will be any modifications or deletes to the primary
key. Call me crazy, but maybe we have to throw a message for primary
key lookups on foreign key tables without indexes. I hate to throw a
message on update/delete rather than create table, but I don't see
another way.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073