thread-index: AcjHDGJP8sJ0g21iQqyTQM5atUmokg==
Content-Class: urn:content-classes:message
Importance: normal
Message-ID: <4847E3A6.6000505@enterprisedb.com>
Date: Thu, 05 Jun 2008 16:01:26 +0300
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
Organization: EnterpriseDB
User-Agent: Mozilla-Thunderbird 2.0.0.12 (X11/20080420)
MIME-Version: 1.0
To: "Simon Riggs" <simon@2ndquadrant.com>
Cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>
Subject: Re: pg_dump restore time and Foreign Keys
References: <1212647003.19964.19.camel@ebony.site>
	<48479381.6020006@enterprisedb.com>
	<1212651938.19964.34.camel@ebony.site>
In-Reply-To: <1212651938.19964.34.camel@ebony.site>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

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