pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aaron Werman <awerman2@hotmail.com>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: atrocious update performance
Date: Mon, 15 Mar 2004 21:42:09 -0500
Message-ID: <LAW10-OE54L42ozbFbN00049ac1@hotmail.com> (raw)
References: <001a01c40ae4$1cdd4160$2500fa0a@CardServices.TCI.com>
	<8912.1079395699@sss.pgh.pa.us>

The original point was about a very slow update of an entire table with a
plan that looped, and over a dozen conditional indices - vs. a 'create as'
in a CPU starved environment. I stand by my statement about observing the
orders of magnitude difference. In theory I agree that the update should be
in the same order of magnitude as the create as, but in practice I disagree.
I also think something is wrong on the logical side (besides FKs, are there
any triggers?) but was responding to the Gordian knot issue of bailing out
of pg.

Can you post a sample extract, Rosser? Otherwise, I'll try to put together a
sample of a slow mass join update.

/Aaron

----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rosser Schwarz" <rschwarz@totalcardinc.com>
Cc: <pgsql-performance@postgresql.org>
Sent: Monday, March 15, 2004 7:08 PM
Subject: Re: [PERFORM] atrocious update performance


> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>



view thread (39+ messages)  latest in thread

Message-ID: <LAW10-OE54L42ozbFbN00049ac1@hotmail.com>
Permalink:  ../LAW10-OE54L42ozbFbN00049ac1@hotmail.com/
Also on:    postgresql.org/message-id/LAW10-OE54L42ozbFbN00049ac1@hotmail.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: awerman2@hotmail.com, rschwarz@totalcardinc.com, tgl@sss.pgh.pa.us
  Subject: Re: atrocious update performance
  In-Reply-To: <LAW10-OE54L42ozbFbN00049ac1@hotmail.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