Thanks, Tom (and David and Josh).
> Well, apparently nobody who knows the code was paying attention, because
> that hasn't been true for some time. ALTER TABLE ADD FOREIGN KEY will
> actually validate the constraint using a query constructed like this
> (cf RI_Initial_Check() in ri_triggers.c):
This was a very helpful pointer, and interesting to me, because I did
a quick look for the source that handled that but didn't find it (not
knowing the Postgres codebase at all). It was kinda weird to me at
first that the way it implements this is by building an SQL string and
then executing that -- at first I would have thought it'd call the
internal functions to do the job. But on second thoughts, this makes
total sense, as that way it gets all the advantages of the query
planner/optimizer for this too.
> It appears the possible explanations for Ben's problem are:
>
> 1. For some reason this query is a lot slower than the one he came up
> with;
>
> 2. The code isn't using this query but is falling back to a row-at-a-time
> check.
Anyway, it's definitely #1 that's happening, as I build the
RI_Initial_Check() query by hand, and it takes just as long as the ADD
CONSTRAINT.
I'll probably hack around it -- in fact, for now I've just dropped the
contraint entirely, as it's not really necessary on this table.
So I guess this is really a side effect of the quirky way we're
dumping and restoring only one schema, and dropping/re-adding
constraints on deployment because of this. Is this a really strange
thing to do -- deploying only one schema (the "static" data) and
dropping/re-adding constraints -- or are there better practices here?
Relatedly, what about best practices regarding inter-schema foreign keys?
-Ben