Re: pg_dump restore time and Foreign Keys

On Thu, 2008-06-05 at 16:01 +0300, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > On Thu, 2008-06-05 at 10:19 +0300, Heikki Linnakangas wrote:
> >> Simon Riggs wrote:
> >>> I'm guessing that the WITHOUT CHECK option would not be acceptable as an
> >>> unprotected trap for our lazy and wicked users. :-)
> >> Yes, that sounds scary.
> >>
> >> Instead, I'd suggest finding ways to speed up the ALTER TABLE ADD
> >> FOREIGN KEY.
> >
> > I managed a suggestion for improving it for integers only, but if
> > anybody has any other ideas, I'm all ears.
>
> Well, one idea would be to allow adding multiple foreign keys in one
> command, and checking them all at once with one SQL query instead of one
> per foreign key. Right now we need one seq scan over the table per
> foreign key, by checking all references at once we would only need one
> seq scan to check them all.

No need. Just parallelise the restore with concurrent psql. Which would
speed up the index creation also. Does Greg have plans for further work?

> >> Or speeding up COPY into a table with foreign keys already
> >> defined. For example, you might want to build an in-memory hash table of
> >> the keys in the target table, instead of issuing a query on each INSERT,
> >> if the target table isn't huge.
> >
> > No, that's not the problem, but I agree that is a problem also.
>
> It is related, because if we can make COPY into a table with foreign
> keys fast enough, we could rearrange dumps so that foreign keys are
> created before loading data. That would save the seqscan over the table
> altogether.

True.

> Thinking about this idea a bit more, instead of loading the whole target
> table into memory, it would probably make more sense to keep a hash
> table as just a cache of the most recent keys that have been referenced.

If you can think of a way of improving hash joins generally, then it
will work for this specific case also.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support