From: Marko Ristola <marko.ristola@kolumbus.fi>
To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: Richard van den Berg <richard.vandenberg@trust-factory.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Foreign key slows down copy/insert
Date: Thu, 14 Apr 2005 19:26:41 +0300
Message-ID: <425E99C1.6050506@kolumbus.fi> (raw)
In-Reply-To: <425E8C0B.8060408@familyhealth.com.au>
References: <425E6045.4060201@trust-factory.com>
<425E792B.4020500@familyhealth.com.au>
<425E7D96.6020201@trust-factory.com>
<425E7F94.4080602@familyhealth.com.au>
<425E8766.8070901@trust-factory.com>
<425E88B7.6000203@familyhealth.com.au>
<425E8B70.2060006@trust-factory.com>
<425E8C0B.8060408@familyhealth.com.au>
About the foreign key performance:
Maybe foreign key checks could be delayed into the COMMIT phase.
In that position, you could check, that there are lots of foreign key
checks
for each foreign key pending, and do the foreign key check for an area
or for the whole table, if it is faster.
I have heard, that the database must be in consistent state after COMMIT,
but it does not have necessarily to be okay inside a transaction.
1. COMMIT wanted
2. If there are lots of foreign key checks pending, do either an area
foreign key check
(join result must be 0 rows), or a full table join.
3. If all foreign key checks are okay, complete the COMMIT operation.
4. If a foreign key check fails, go into the ROLLBACK NEEDED state.
Maybe Tom Lane meant the same.
set option delayed_foreign_keys=true;
BEGIN;
insert 1000 rows.
COMMIT;
Regards,
Marko Ristola
Christopher Kings-Lynne wrote:
>> My problem with this really is that in my database it is hard to
>> predict which inserts will be huge (and thus need FKs dissabled), so
>> I would have to code it around all inserts. Instead I can code my own
>> integirty logic and avoid using FKs all together.
>
>
> Just drop the fk and re-add it, until postgres gets more smarts.
>
> Chris
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
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-performance@postgresql.org
Cc: marko.ristola@kolumbus.fi, chriskl@familyhealth.com.au, richard.vandenberg@trust-factory.com
Subject: Re: Foreign key slows down copy/insert
In-Reply-To: <425E99C1.6050506@kolumbus.fi>
* 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