pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Greg Stark <gsstark@mit.edu>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
Cc: 'Postgresql Performance' <pgsql-performance@postgresql.org>
Subject: Re: atrocious update performance
Date: 16 Mar 2004 02:31:06 -0500
Message-ID: <871xnt1c5h.fsf@stark.xeocode.com> (raw)
In-Reply-To: <001501c40ad2$b3f7ade0$2500fa0a@CardServices.TCI.com>
References: <001501c40ad2$b3f7ade0$2500fa0a@CardServices.TCI.com>


"Rosser Schwarz" <rschwarz@totalcardinc.com> writes:

> Actually, there are no foreign keys to those columns.  Once they're
> populated, I'll apply a foreign key constraint and they'll refer to the
> appropriate row in the prod and subprod tables, but nothing will 
> reference account.cust.[sub]prodid.  There are, of course, several foreign
> keys referencing account.cust.custid.

Just to be clear, the foreign key constraints they're worrying about are not
constraints on the table you're updating. They're constraints on other tables
referring to the table you're updating. 

Since you're updating the column here postgres has to be sure nothing is
referring to the old value you're obliterating, and to do that it has to
select for possible records in the referencing tables referring to the value.
If there are any references in other tables referring to this column then you
need an index on the column in the referencing table to be able to update the
column in referenced table efficiently.

-- 
greg




view thread (39+ messages)  latest in thread

Message-ID: <871xnt1c5h.fsf@stark.xeocode.com>
Permalink:  ../871xnt1c5h.fsf@stark.xeocode.com/
Also on:    postgresql.org/message-id/871xnt1c5h.fsf@stark.xeocode.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: gsstark@mit.edu, rschwarz@totalcardinc.com
  Subject: Re: atrocious update performance
  In-Reply-To: <871xnt1c5h.fsf@stark.xeocode.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