From: Rod Taylor <pg@rbt.ca>
To: Rosser Schwarz <rschwarz@totalcardinc.com>
Cc: Postgresql Performance <pgsql-performance@postgresql.org>
Subject: Re: atrocious update performance
Date: Mon, 15 Mar 2004 16:06:33 -0500
Message-ID: <1079384792.33643.4.camel@jester> (raw)
In-Reply-To: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com>
References: <001401c40acc$1e4e7180$2500fa0a@CardServices.TCI.com>
> # explain update account.cust set prodid = tempprod.prodid
> where tempprod.did = origid;
>
> Merge Join (cost=0.00..232764.69 rows=4731410 width=252)
> Merge Cond: (("outer".origid)::text = ("inner".did)::text)
> -> Index Scan using ix_origid on cust (cost=0.00..94876.83
> rows=4731410 width=244)
> -> Index Scan using ix_did on tempprod (cost=0.00..66916.71
> rows=4731410 width=18)
I'm going to hazard a guess and say you have a number of foreign keys
that refer to account.cust.prodid? This is probably the time consuming
part -- perhaps even a missing index on one of those keys that refers to
this field.
Going the other way should be just as good for your purposes, and much
faster since you're not updating several foreign key'd fields bound to
account.cust.prodid.
UPDATE tempprod.prodid = prodid
FROM account.cust
WHERE temprod.did = cust.origid;
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: pg@rbt.ca, rschwarz@totalcardinc.com
Subject: Re: atrocious update performance
In-Reply-To: <1079384792.33643.4.camel@jester>
* 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