From: Brendan Duddridge <brendan@clickspace.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Greg Stark <gsstark@mit.edu>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: App very unresponsive while performing simple update
Date: Sun, 28 May 2006 13:17:07 -0600
Message-ID: <3C756C36-DC66-4D13-B2CE-99CFCE7BF390@clickspace.com> (raw)
In-Reply-To: <17733.1148832292@sss.pgh.pa.us>
References: <961F6F02-4B00-494C-B071-6DF76A305C14@clickspace.com>
<21B15664-F4EE-462C-88B4-EEEB2B4A979F@clickspace.com>
<874pzanvk6.fsf@stark.xeocode.com>
<17733.1148832292@sss.pgh.pa.us>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brendan@clickspace.com, tgl@sss.pgh.pa.us, gsstark@mit.edu
Subject: Re: App very unresponsive while performing simple update
In-Reply-To: <3C756C36-DC66-4D13-B2CE-99CFCE7BF390@clickspace.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox