DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:reply-to:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
	b=TuhmPl1+FSSrD/dzSB1zJUqQPsJ/kdZOZtDTWS5HmKezcpyQ5rdNdwJF7EiHQDCbzlbhen2SF1BsrBeCbn/UHJsO/VbOFeUGLwWzVmg/VAQx/48guuc2+yqQCWBV3eHcQJygUzIoWCznG5cCg3Vz6Nq3CjjGkNASNf5qWQTJFTY=
Message-ID: <84d3067d0506212312775b3fa3@mail.gmail.com>
Date: Wed, 22 Jun 2005 18:12:34 +1200
From: Colin Taylor <colin.taylor@gmail.com>
Reply-To: Colin Taylor <colin.taylor@gmail.com>
To: pgsql-performance@postgresql.org
Subject: slow simple update?
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi there,

I'm doing an update of ~30,000 rows and she takes about 15mins on
pretty good hardware, even just after a vacuum analyze.
I was hoping some kind soul could offer some performance advice. Do I
just have too many indexes? Or am I missing some trick with the nulls?


MY QUERY
=3D=3D=3D=3D=3D=3D=3D=3D
update bob.product_price set thru_date =3D '2005-06-22 22:08:49.957'
where thru_date is null;


MY TABLE
=3D=3D=3D=3D=3D=3D=3D=3D=3D
                   Table "bob.product_price"
           Column            |           Type           | Modifiers=20
-----------------------------+--------------------------+-----------
 product_id                  | character varying(20)    | not null
 product_price_type_id       | character varying(20)    | not null
 currency_uom_id             | character varying(20)    | not null
 product_store_id            | character varying(20)    | not null
 from_date                   | timestamp with time zone | not null
 thru_date                   | timestamp with time zone |=20
 price                       | numeric(18,2)            |=20
 created_date                | timestamp with time zone |=20
 created_by_user_login       | character varying(255)   |=20
 last_modified_date          | timestamp with time zone |=20
 last_modified_by_user_login | character varying(255)   |=20
 last_updated_stamp          | timestamp with time zone |=20
 last_updated_tx_stamp       | timestamp with time zone |=20
 created_stamp               | timestamp with time zone |=20
 created_tx_stamp            | timestamp with time zone |=20

Indexes:
---------
pk_product_price primary key btree
 (product_id, product_price_type_id, currency_uom_id,
product_store_id, from_date),
prdct_prc_txcrts btree (created_tx_stamp),
prdct_prc_txstmp btree (last_updated_tx_stamp),
prod_price_cbul btree (created_by_user_login),
prod_price_cuom btree (currency_uom_id),
prod_price_lmbul btree (last_modified_by_user_login),
prod_price_prod btree (product_id),
prod_price_pst btree (product_store_id),
prod_price_type btree (product_price_type_id)

Foreign Key constraints:=20
-------------------------
prod_price_prod FOREIGN KEY (product_id) REFERENCES bob.product(product_id)=
=20
 ON UPDATE NO ACTION ON DELETE NO ACTION,
prod_price_type FOREIGN KEY (product_price_type_id) REFERENCES
bob.product_price_type(product_price_type_id)
 ON UPDATE NO ACTION ON DELETE NO ACTION,
prod_price_cuom FOREIGN KEY (currency_uom_id) REFERENCES bob.uom(uom_id)=20
 ON UPDATE NO ACTION ON DELETE NO ACTION,
prod_price_pst FOREIGN KEY (product_store_id) REFERENCES
bob.product_store(product_store_id)
 ON UPDATE NO ACTION ON DELETE NO ACTION,
prod_price_cbul FOREIGN KEY (created_by_user_login) REFERENCES
bob.user_login(user_login_id)
 ON UPDATE NO ACTION ON DELETE NO ACTION,
prod_price_lmbul FOREIGN KEY (last_modified_by_user_login) REFERENCES
bob.user_login(user_login_id)
 ON UPDATE NO ACTION ON DELETE NO ACTION