On 6/19/06 3:24 PM, "Scott Marlowe" <smarlowe@xxxxxxxxxxxxxxxxx> wrote: > Are you sure that's really the problem? Do you have indexes on the > referring tables (i.e. the foreign key that points to the other table's > primary key). Not having an index on the subordinate table makes each > and every check on the FK->PK relationship require a seq scan of the > subordinate table. Yes, I am sure I have the indexes for both sides. (I just double checked). If not, wouldn't I have abysmal load performance in production, or does that function differently? The constraint that just finished after 18+ hours has a non-composite index on both sides. The other constraint: ADD CONSTRAINT "$2" FOREIGN KEY (recipient) REFERENCES addresses(address_key); has a normal index on address_key. The recipient index is (recipient, date) composite index. This index has always been used when searching on just recipient, and the last time I rebuilt the database (a year ago), the FK addition was inline with expectations. In every case, it takes about the same time to add the foreign key constraint as to create the index, maybe a little more. This is true regardless of whether one of the indexes is composite or not. One constraint build just finished after a little more than 18 hours. The table where the constraint is being created has about 900 million rows. The 'references x' table has about 200 million rows. It churns along eventually eating up about 50 GB or so in the 'base' directory. When that stops growing, it took maybe another hour or two to complete. I'd just like to be able to tell it to simply add the constraint without doing any checking. I know the data is consistent. I just dumped it from a database that has referential integrity enabled. Even if there were an error that crept in to the old database, I don't care - just add the constraint so I can get back online. Right now I'm looking at around 80-90 hours total to do a reload. What's it going to be a year or two from now? I could be out of commission for a week. Wes
