thread-index: AcjHERklR/l0swWlTOm5QPs96nEs5A==
Content-Class: urn:content-classes:message
Importance: normal
Message-ID: <4847EB8E.20600@enterprisedb.com>
Date: Thu, 05 Jun 2008 16:35:10 +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>,
	"Greg Stark" <greg.stark@enterprisedb.com>
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>
	<4847E3A6.6000505@enterprisedb.com>
	<1212672470.19964.71.camel@ebony.site>
In-Reply-To: <1212672470.19964.71.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 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