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: Mon, 15 Mar 2004 19:08:19 -0500
Message-ID: <8912.1079395699@sss.pgh.pa.us> (raw)
In-Reply-To: <001a01c40ae4$1cdd4160$2500fa0a@CardServices.TCI.com>
References: <001a01c40ae4$1cdd4160$2500fa0a@CardServices.TCI.com>

"Rosser Schwarz" <rschwarz@totalcardinc.com> writes:
>> You can create a new table using 'create table as' to produce your
>> target results. This is real fast ...
>> I often see 2 orders of magnitude improvement doing this, and no
>> need to vacuum.

> Indeed:
> "Query returned successfully with no result in 582761 ms."
> Though I must say, ten minutes is nominally more than two orders of
> mangitude performance improvement, versus several days.

Hm.  There is no way that inserting a row is two orders of magnitude
faster than updating a row --- they both require storing a new row and
making whatever index entries are needed.  The only additional cost of
the update is finding the old row (not a very big deal AFAICS in the
examples you gave) and marking it deleted (definitely cheap).  So
there's something awfully fishy going on here.

I'm inclined to suspect an issue with foreign-key checking.  You didn't
give us any details about foreign key relationships your "cust" table is
involved in --- could we see those?  And the schemas of the other tables
involved?

Also, exactly which PG version is this?

			regards, tom lane



view thread (39+ messages)  latest in thread

Message-ID: <8912.1079395699@sss.pgh.pa.us>
Permalink:  ../8912.1079395699@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/8912.1079395699@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: <8912.1079395699@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