pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Tue, 16 Mar 2004 13:04:46 -0500
Message-ID: <26148.1079460286@sss.pgh.pa.us> (raw)
In-Reply-To: <001d01c40b7c$b80c98a0$2500fa0a@CardServices.TCI.com>
References: <001d01c40b7c$b80c98a0$2500fa0a@CardServices.TCI.com>

"Rosser Schwarz" <rschwarz@totalcardinc.com> writes:
> As for foreign keys, three tables refer to account.cust; all of them
> refer to account.cust.custid, the pk.  One of those tables has several
> hundred thousand rows, many more to come; the others are empty.  Unless
> I've woefully misunderstood, the presence or absence of a foreign key
> referring to one column should be moot for updates writing another
> column, shouldn't it?

Well, that is the crux of the issue, and also why I was asking about
versions.  It's only been since 7.3.4 or so that we skip checking FKs on
update.

Looking at the code, though, the update check is only skipped if the
previous version of the row predates the current transaction.
(Otherwise we can't be sure that the value was ever checked.)  This
means that slow FK checks could be your problem if the application is
set up to issue multiple UPDATEs affecting the same row(s) during a
single transaction.  I'm not clear on whether that applies to you or not.

And anyway the bottom line is: have you got indexes on the columns
*referencing* account.cust.custid?  If not you probably ought to add
them, since without them there will definitely be some slow cases.

			regards, tom lane



view thread (39+ messages)  latest in thread

Message-ID: <26148.1079460286@sss.pgh.pa.us>
Permalink:  ../26148.1079460286@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/26148.1079460286@sss.pgh.pa.us

 · 

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: tgl@sss.pgh.pa.us, rschwarz@totalcardinc.com
  Subject: Re: atrocious update performance
  In-Reply-To: <26148.1079460286@sss.pgh.pa.us>

* 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