Re: atrocious update performance

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(at)sss(dot)pgh(dot)pa(dot)us>
To: "Rosser Schwarz" <rschwarz(at)totalcardinc(dot)com>
Cc: <pgsql-performance(at)postgresql(dot)org>
Sent: Monday, March 15, 2004 7:08 PM
Subject: Re: [PERFORM] atrocious update performance

> "Rosser Schwarz" <rschwarz(at)totalcardinc(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>