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

Re: App very unresponsive while performing simple update

From: Brendan Duddridge <brendan(at)clickspace(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: App very unresponsive while performing simple update
Date: 2006-05-28 19:17:07
Message-ID: 3C756C36-DC66-4D13-B2CE-99CFCE7BF390@clickspace.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

Thanks for your replies.

We are using PostgreSQL 8.1.3 on OS X Server.

We do have foreign keys on other tables that reference the product  
table. Also, there will be updates going on at the same time as this  
update. When anyone clicks on a product details link, we issue an  
update statement to increment the click_count on the product. e.g.  
update product set click_count = click_count + 1;

There are 1.2 million rows in this table and my update will affect  
200,000 of them.

We do have indexes on all foreign keys that reference the product table.

Here's what our product table looks like:

                         Table "public.product"
            Column            |            Type             | Modifiers
------------------------------+-----------------------------+-----------
click_count                  | integer                     |
date_created                 | timestamp without time zone | not null
date_modified                | timestamp without time zone |
date_of_last_keyphrase_match | timestamp without time zone |
ean                          | character varying(32)       |
gtin                         | character varying(32)       |
home_category_id             | integer                     |
is_active                    | character varying(5)        |
is_featured                  | character varying(5)        |
is_hungry                    | character varying(5)        |
isbn                         | character varying(32)       |
manufacturer_id              | integer                     |
media_for_clipboard_id       | integer                     |
media_for_detail_id          | integer                     |
media_for_thumbnail_id       | integer                     |
mpn                          | character varying(512)      |
product_id                   | integer                     | not null
status_code                  | character varying(32)       |
unsps_code                   | bigint                      |
upc                          | character varying(32)       |
riding_id                    | integer                     |
name_en                      | character varying(512)      |
name_fr                      | character varying(512)      |
short_description_en         | character varying(2048)     |
short_description_fr         | character varying(2048)     |
long_description_en          | text                        |
long_description_fr          | text                        |
Indexes:
     "product_pk" PRIMARY KEY, btree (product_id)
     "product__active_status_idx" btree (is_active, status_code)
     "product__additional_0__idx" btree (riding_id)
     "product__date_created_idx" btree (date_created)
     "product__date_modified_idx" btree (date_modified)
     "product__date_of_last_keyphrase_match_idx" btree  
(date_of_last_keyphrase_match)
     "product__home_category_id_fk_idx" btree (home_category_id)
     "product__hungry_idx" btree (is_hungry)
     "product__lower_name_en_idx" btree (lower(name_en::text))
     "product__lower_name_fr_idx" btree (lower(name_fr::text))
     "product__manufacturer_id_fk_idx" btree (manufacturer_id)
     "product__manufacturer_id_mpn_idx" btree (manufacturer_id, mpn)
     "product__media_for_clipboard_id_fk_idx" btree  
(media_for_clipboard_id)
     "product__media_for_detail_id_fk_idx" btree (media_for_detail_id)
     "product__media_for_thumbnail_id_fk_idx" btree  
(media_for_thumbnail_id)
     "product__upc_idx" btree (upc)
     "product_additional_2__idx" btree (is_active, status_code) WHERE  
is_active::text = 'true'::text AND status_code::text = 'complete'::text
Foreign-key constraints:
     "product_homecategory_fk" FOREIGN KEY (home_category_id)  
REFERENCES category(category_id) DEFERRABLE INITIALLY DEFERRED
     "product_manufacturer_fk" FOREIGN KEY (manufacturer_id)  
REFERENCES manufacturer(manufacturer_id) DEFERRABLE INITIALLY DEFERRED
     "product_mediaforclipboard_fk" FOREIGN KEY  
(media_for_clipboard_id) REFERENCES media(media_id) DEFERRABLE  
INITIALLY DEFERRED
     "product_mediafordetail_fk" FOREIGN KEY (media_for_detail_id)  
REFERENCES media(media_id) DEFERRABLE INITIALLY DEFERRED
     "product_mediaforthumbnail_fk" FOREIGN KEY  
(media_for_thumbnail_id) REFERENCES media(media_id) DEFERRABLE  
INITIALLY DEFERRED


____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 |  brendan(at)clickspace(dot)com

ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB  T2G 0V9

http://www.clickspace.com

On May 28, 2006, at 10:04 AM, Tom Lane wrote:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> What queries are those two processes executing? And what foreign  
>> keys do you
>> have on the product table or elsewhere referring to the product  
>> table? And
>> what indexes do you have on those columns?
>
> And what PG version is this?  Alvaro fixed the
> foreign-keys-take-exclusive-locks problem in 8.1 ...
>
> 			regards, tom lane
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>



In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2006-05-28 21:32:19
Subject: Re: App very unresponsive while performing simple update
Previous:From: Greg StarkDate: 2006-05-28 17:55:54
Subject: Re: App very unresponsive while performing simple update

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