agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Heikki Linnakangas <heikki@enterprisedb.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Cc: Greg Stark <greg.stark@enterprisedb.com>
Subject: Re: pg_dump restore time and Foreign Keys
Date: Thu, 05 Jun 2008 16:35:10 +0300
Message-ID: <4847EB8E.20600@enterprisedb.com> (raw)
In-Reply-To: <1212672470.19964.71.camel@ebony.site>
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>
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
view thread (40+ messages) latest in thread
Message-ID: <4847EB8E.20600@enterprisedb.com>
Permalink: ../../4847EB8E.20600@enterprisedb.com/
Also on: postgresql.org/message-id/4847EB8E.20600@enterprisedb.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: heikki@enterprisedb.com, simon@2ndquadrant.com, greg.stark@enterprisedb.com
Subject: Re: pg_dump restore time and Foreign Keys
In-Reply-To: <4847EB8E.20600@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox