pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Richard van den Berg <richard.vandenberg@trust-factory.com>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Foreign key slows down copy/insert
Date: Thu, 14 Apr 2005 07:45:33 -0700 (PDT)
Message-ID: <20050414074154.M38465@megazone.bigpanda.com> (raw)
In-Reply-To: <425E7D96.6020201@trust-factory.com>
References: <425E6045.4060201@trust-factory.com>
	<425E792B.4020500@familyhealth.com.au>
	<425E7D96.6020201@trust-factory.com>


On Thu, 14 Apr 2005, Richard van den Berg wrote:

> Hello Chris,
>
> Thanks for your answers.
>
> Christopher Kings-Lynne wrote:
> > Deferring makes no difference to FK checking speed...
>
> But why then is the speed acceptable if I copy and then manually add the
> FK? Is the check done by the FK so much different from when it is done
> automatically using an active deffered FK?

Yes, because currently the check done by the FK on an insert type activity
is a per-row inserted check while the check done when adding a FK acts on
the entire table in a go which allows better optimization of that case
(while generally being worse on small number inserts especially on large
tables).  At some point, if we can work out how to do all the semantics
properly, it'd probably be possible to replace the insert type check with
a per-statement check which would be somewhere in between.  That requires
access to the affected rows inside the trigger which I don't believe is
available currently.



view thread (24+ messages)  latest in thread

Message-ID: <20050414074154.M38465@megazone.bigpanda.com>
Permalink:  ../20050414074154.M38465@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20050414074154.M38465@megazone.bigpanda.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-performance@postgresql.org
  Cc: sszabo@megazone.bigpanda.com, richard.vandenberg@trust-factory.com, chriskl@familyhealth.com.au
  Subject: Re: Foreign key slows down copy/insert
  In-Reply-To: <20050414074154.M38465@megazone.bigpanda.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