On Sun, 2006-06-18 at 22:41, Wes wrote:
> Is there a way to add a foreign key constraint without having to wait for it
> to check the consistency of all existing records? If a database is being
> reloaded (pg_dumpall then load), it really shouldn't be necessary to check
> the referential integrity - or at least I should be able to stipulate that I
> am accepting that risk.
>
> My database reload is currently taking about 6 hours to load the data, 42
> hours to reindex, and about another 40 hours or so to check the foreign key
> constraints (about 1.2 billion rows). That's a very long time to be out of
> commission. I'd really like to eliminate that second 40 hours so I can get
> it down to a normal weekend.
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.