X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2055B9FA510 for ; Sun, 28 May 2006 16:17:33 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 57339-09 for ; Sun, 28 May 2006 16:17:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.clickspace.com (router2.clickspace.com [65.110.166.227]) by postgresql.org (Postfix) with ESMTP id 26D559F9F3D for ; Sun, 28 May 2006 16:17:24 -0300 (ADT) Received: from [10.0.1.4] ([68.147.204.179]) (authenticated user brendan@clickspace.com) by mail.clickspace.com (using TLSv1/SSLv3 with cipher RC4-SHA (128 bits)); Sun, 28 May 2006 13:17:14 -0600 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> Mime-Version: 1.0 (Apple Message framework v750) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <3C756C36-DC66-4D13-B2CE-99CFCE7BF390@clickspace.com> Cc: Greg Stark , PostgreSQL Performance Content-Transfer-Encoding: 7bit From: Brendan Duddridge Subject: Re: App very unresponsive while performing simple update Date: Sun, 28 May 2006 13:17:07 -0600 To: Tom Lane X-Mailer: Apple Mail (2.750) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/508 X-Sequence-Number: 19295 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 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 >