Re: Constraint question

"Jim C. Nasby" <jnasby(at)pervasive(dot)com> writes:
> On Sun, May 14, 2006 at 10:50:50PM -0400, Tom Lane wrote:
>> Enzo Daddario <enzo(at)pienetworks(dot)com> writes:
>>> I am now required the remove thousands of obsolete records from table
>>> "X" and even though I have removed all related rows in all related
>>> tables, the deletion of rows in table "X" is taking WAY TOO LONG.
>>
>> You need to put indexes on the referencing columns. Then start a fresh
>> session for doing the actual deletes (FK check plans are usually cached
>> per-session).

> Would SET CONSTRAINTS ... DEFERRED not help, or does it still use the
> same machinery to do the checking, regardless of how much data there is
> to check?

It's the same machinery. We've speculated about having the thing switch
over to doing a full-table recheck (comparable to what ADD CONSTRAINT
FOREIGN KEY does) once the number of pending individual row checks
exceeds some threshold, but that's not done yet --- and it's not clear
how to do it in a concurrent fashion, so don't hold your breath ...

regards, tom lane