From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: pg_dump restore time and Foreign Keys
Date: Thu, 05 Jun 2008 16:01:26 +0300
Message-ID: <4847E3A6.6000505@enterprisedb.com> (raw)
In-Reply-To: <1212651938.19964.34.camel@ebony.site>
References: <1212647003.19964.19.camel@ebony.site>
<48479381.6020006@enterprisedb.com>
<1212651938.19964.34.camel@ebony.site>
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
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
Subject: Re: pg_dump restore time and Foreign Keys
In-Reply-To: <4847E3A6.6000505@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 DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox