From: | Denis <denis(dot)sailer(at)yellowbook(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Update using primary key slow |
Date: | 2005-10-27 15:41:22 |
Message-ID: | Xns96FC6CBBEC0CDdenissaileryellowboo@200.46.204.72 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
The following update was captured in the database log and the elapsed time
was 1058.956 ms. A later explain analyze shows total run time of 730 ms.
Although isn't the actual time to update the row 183 ms. Where is the
other 547 ms coming from? Updating the two secondary indexes??
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-1] LOG: duration:
1058.956 ms statement: UPDATE CONTRACT SET CUSTOMER_KEY = 143567
,SOURCE_CODE_KEY = null ,PRIOR_CONTRACT =
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-2]
'265985' ,PRIOR_CONTRACT_ELITE = null ,CONTRACT_NEW = 'N' ,RENEWAL_CONTRACT
= '1373990' ,RENEWAL_CONTRACT_ELITE = null
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-3] ,CONTRACT_DROPPED =
'N' ,TOTAL_SALE_DOLLARS = 3492.00 ,TARGET_DOLLARS = 3576
,ASSIGN_DOLLARS_OVERRIDE = null ,BOOK_KEY = 160
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-4] ,PUB_SEQUENCE = 25
,DROP_DATE = null ,OUT_OF_BUSINESS = 'N' ,RENEWAL_SALESREP_KEY = 3639
,SALESREP_KEY = 3639 ,NEW_CATEGORY =
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-5]
'NEW_INSERT' ,PENDING_DELETE_DATE = null ,CLIENT_NAME = null ,DATE_SOLD =
'2004-08-30' ,DATE_RECEIVED = '2004-09-03'
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-6] ,DATE_ENTERED =
'2004-09-07' ,DATE_SHELLED = null ,DATE_APPROVED = '2004-09-09' WHERE
REGION_KEY = 14 AND CONTRACT_KEY =
Oct 27 08:09:48 ybcdrdwdb01 postgres[12315]: [145-7] 1070135
The explain for this update is as follows.
dw=# begin;
BEGIN
dw=#
dw=# explain analyze UPDATE CONTRACT SET CUSTOMER_KEY = 143567,
SOURCE_CODE_KEY = null ,
dw-# PRIOR_CONTRACT = '265985' ,PRIOR_CONTRACT_ELITE = null ,CONTRACT_NEW =
'N' ,RENEWAL_CONTRACT = '1373990' ,RENEWAL_CONTRACT_ELITE = null
dw-# ,CONTRACT_DROPPED = 'N' ,TOTAL_SALE_DOLLARS = 3492.00 ,TARGET_DOLLARS
= 3576 ,ASSIGN_DOLLARS_OVERRIDE = null ,BOOK_KEY = 160
dw-# ,PUB_SEQUENCE = 25 ,DROP_DATE = null ,OUT_OF_BUSINESS =
'N' ,RENEWAL_SALESREP_KEY = 3639 ,SALESREP_KEY = 3639
dw-# ,NEW_CATEGORY = 'NEW_INSERT' ,PENDING_DELETE_DATE = null ,CLIENT_NAME
= null ,DATE_SOLD = '2004-08-30' ,DATE_RECEIVED = '2004-09-03'
dw-# ,DATE_ENTERED = '2004-09-07' ,DATE_SHELLED = null ,DATE_APPROVED =
'2004-09-09'
dw-# WHERE REGION_KEY = 14 AND CONTRACT_KEY = 1070135;
QUERY PLAN
---------------------------------------------------------------------------
------------------------------------------------
Index Scan using contract_pkey on contract (cost=0.00..10.61 rows=3
width=115) (actual time=0.181..0.183 rows=1 loops=1)
Index Cond: ((contract_key = 1070135) AND (region_key = 14))
Total runtime: 0.730 ms
(3 rows)
dw=# rollback;
ROLLBACK
Here is the table and index definitions
dw=# \d contract
Table "ods.contract"
Column | Type | Modifiers
-------------------------+-----------------------------+---------------
contract_key | integer | not null
customer_key | integer | not null
source_code_key | integer |
prior_contract | character varying(20) |
prior_contract_elite | character varying(20) |
renewal_contract | character varying(20) |
contract_dropped | character varying(1) | not null
renewal_contract_elite | character varying(20) |
total_sale_dollars | numeric(9,2) | not null
assign_dollars_override | numeric(9,2) |
target_dollars | numeric(9,2) |
book_key | integer | not null
pub_sequence | integer |
drop_date | timestamp without time zone |
out_of_business | character varying(1) | not null
salesrep_key | integer |
renewal_salesrep_key | integer |
new_category | character varying(20) |
region_key | integer | not null
contract_new | character varying(1) | not null
pending_delete_date | timestamp without time zone |
client_name | character varying(150) |
fuzzy_client_name | character varying(150) |
last_update_date | timestamp without time zone | default now()
date_sold | date |
date_received | date |
date_entered | date |
date_shelled | date |
date_approved | date |
Indexes:
"contract_pkey" primary key, btree (contract_key, region_key)
"XIE1_Contract" btree (region_key, book_key, pub_sequence)
"XIE2_Contract" btree (customer_key, region_key)
The table contains approximately 5 million rows
Postgres version is PostgreSQL 7.4.3 on i686-pc-linux-gnu, compiled by GCC
gcc (GCC) 3.2.2
The machine has 4 Intel Xeon 3.0GHz processors and 3GB of memory
shared_buffers = 16384
sort_mem = 8192
vacuum_mem = 8192
effective_cache_size = 262144
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-10-27 15:56:49 | Re: how postgresql request the computer resources |
Previous Message | Richard Huxton | 2005-10-27 15:40:39 | Re: Perfomance of views |