Re: pg_dump restore time and Foreign Keys

Simon Riggs wrote:
> On Thu, 2008-06-05 at 16:01 +0300, Heikki Linnakangas wrote:
>> 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.

True, you could do that.

>  Does Greg have plans for further work?

I believe he's busy with other stuff at the moment.

>> 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.

Individual RI checks performed on inserts/COPY don't do a hash join. The 
bulk check done by ALTER TABLE ADD FOREIGN KEY does, but that's 
different issue.

This hash table would be a specific trick to speed up RI checks. If 
you're anyway I/O bound, it wouldn't help, and you'd already be better 
off creating the foreign key first and loading the data after that.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com