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@xxxxxxxxxxxxxx
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@xxxxxxx> 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
