Re: pg_restore and FK constraints with large dbs

On Sun, 16 Nov 2003, ow wrote:

> --- ow <oneway_111(at)yahoo(dot)com> wrote:
> > Perhaps I should clarify.
> >
> > First, I ran pg_dump to extract schema and data *together*. Then I ran
> > pg_restore to restore the db. It took about 1 hour to create tables and copy
> > the data, then about 40 min to create indexes, then pg_restore spent 4.5
> > hours
> > checking one (1) FK constraint (80M table with FKs against 20K table with
> > PKs)
> >
> > 4.5 hours to check one FK constraint - this is what I meant by bad
> > performance.
> > I'm looking for a way to suspend FK checks since data is coming from pg_dump
> > and, hence, it's clean.
> >
>
> No solution? Thanks

By the way, what does your schema look like? I created an 80M row fk
table and 20K row pk table with an int4 key between them and indexes on
the two key fields. It took about 25 minutes on my not terribly fast
system using 7.4b5 to make the foreign key between them. It might have
been faster if I'd raised sort_mem to something larger than 8192.