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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
