Skip site navigation (1) Skip section navigation (2)

Re: slow simple update?

From: "philippe ventrillon" <philippe(dot)ventrillon(at)fr(dot)netcentrex(dot)net>
To: "'Colin Taylor'" <colin(dot)taylor(at)gmail(dot)com>,<pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow simple update?
Date: 2005-06-30 12:38:36
Message-ID: 20050630123836.9DFC7528D5@svr1.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-performance
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(at)postgresql(dot)org
[mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Colin Taylor
Sent: mercredi 22 juin 2005 08:13
To: pgsql-performance(at)postgresql(dot)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


In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2005-06-30 13:31:01
Subject: Re: Vacuum becomes slow
Previous:From: Milan SekaninaDate: 2005-06-30 10:28:55
Subject: ODBC driver over network very slow

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group