pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Kevin Brown <kevin@sysexperts.com>
To: pgsql-hackers@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Foreign key performance
Date: Thu, 17 Apr 2003 22:11:33 -0700
Message-ID: <20030418051133.GK1833@filer> (raw)

I'm using 7.3.2 on Linux, with a decent amount of muscle behind it
(1.5 GHz PPro CPU, 1G mem, 20M/sec disks, xlog on different disk than
data).

I've got a database that has several foreign keys, and I'm copying a
bunch of data from an MS-SQL server into it via Perl DBI.  I noticed
that inserts into this database are very slow, on the order of 100 per
second on this hardware.  All the inserts are happening in a single
transaction.  The postmaster I'm connected to appears to be CPU
limited, as it's pegging the CPU at a constant 85 percent or more.

I have no problem with that under normal circumstances (i.e., the
foreign key constraints are actively being enforced): it may well be
the nature of foreign keys, but the problem is this: all the keys are
DEFERRABLE INITIALLY DEFERRED and, on top of that, the Perl program
will SET CONSTRAINTS ALL DEFERRED at the beginning of the transaction.

If I remove all the foreign key constraints, my performance goes up to
700 inserts per second!

Why isn't the insert performance with all the constraints deferred
approximating that of the performance I get without the foreign keys??
If anything, I should get a big delay at transaction commit time while
all the foreign key constraints are checked (and, indeed, I get that
too), but the performance during the transaction prior to the commit
should be the same as it is without the foreign key constraints.

It's almost as if the foreign key constraints are being invoked and
the results ignored during the inserts...

In essence, this smells like a bug to me, but I don't know enough
about the internals to really call it that.


Any ideas on what can be done about this?




-- 
Kevin Brown					      kevin@sysexperts.com




view thread (18+ messages)  latest in thread

Message-ID: <20030418051133.GK1833@filer>
Permalink:  ../20030418051133.GK1833@filer/
Also on:    postgresql.org/message-id/20030418051133.GK1833@filer

 · 

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: kevin@sysexperts.com, pgsql-hackers@postgresql.org
  Subject: Re: Foreign key performance
  In-Reply-To: <20030418051133.GK1833@filer>

* 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