Re: App very unresponsive while performing simple update - Mailing list 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@clickspace.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@mit.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
>