Re: pg_dump restore time and Foreign Keys

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.

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

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.

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