Thread: slow simple update?
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
========
update bob.product_price set thru_date = '2005-06-22 22:08:49.957'
where thru_date is null;
MY TABLE
=========
Table "bob.product_price"
Column | Type | Modifiers
-----------------------------+--------------------------+-----------
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 |
price | numeric(18,2) |
created_date | timestamp with time zone |
created_by_user_login | character varying(255) |
last_modified_date | timestamp with time zone |
last_modified_by_user_login | character varying(255) |
last_updated_stamp | timestamp with time zone |
last_updated_tx_stamp | timestamp with time zone |
created_stamp | timestamp with time zone |
created_tx_stamp | timestamp with time zone |
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:
-------------------------
prod_price_prod FOREIGN KEY (product_id) REFERENCES bob.product(product_id)
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)
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
You should provide a bit more details on what happens if you want people to
help you.
Tipically you will be asked an explain analyze of your query.
As a first tip if your table contains much more than 30.000 rows you could
try to set up a partial index with
thru_date is null condition.
regards
--
Philippe
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Colin Taylor
Sent: mercredi 22 juin 2005 08:13
To: pgsql-performance@postgresql.org
Subject: [PERFORM] slow simple update?
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
========
update bob.product_price set thru_date = '2005-06-22 22:08:49.957'
where thru_date is null;
MY TABLE
=========
Table "bob.product_price"
Column | Type | Modifiers
-----------------------------+--------------------------+-----------
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 |
price | numeric(18,2) |
created_date | timestamp with time zone |
created_by_user_login | character varying(255) |
last_modified_date | timestamp with time zone |
last_modified_by_user_login | character varying(255) |
last_updated_stamp | timestamp with time zone |
last_updated_tx_stamp | timestamp with time zone |
created_stamp | timestamp with time zone |
created_tx_stamp | timestamp with time zone |
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:
-------------------------
prod_price_prod FOREIGN KEY (product_id) REFERENCES bob.product(product_id)
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) 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster