Re: Quick select, slow update - help with performance problems

On Tuesday 01 July 2008 12:17, Richard Huxton wrote:
> Gary Stainburn wrote:
> > update used_diary set
> > ud_valet_completed=now(), ud_valet_completed_by=25
> > where ud_valet_completed is null and
> > ud_valet_required < CURRENT_DATE-'7 days'::interval
> >
> > is still running after approx 1 1/2 minutes. I've noticed that other
> > updates also seem to take a long time.
>
> Do you have any foreign keys referencing used_diary? Do they have the
> correct indexes on the referencing tables?
> Any on-update triggers?
>

After about 5 minutes I Ctrl+C'd and then re-sent the update and it completed
in about 5 seconds, so I don't know what happened there.

Below is the \d for the table. I can't see how updating those two fields would
cause a problem though.

I've also included the explain, which looks very straight-forward

goole=# \d used_diary
Table "public.used_diary"
Column | Type |
Modifiers
---------------------------+-----------------------------+---------------------
-------------------------------------------------
ud_id | integer | not null default
nex
tval(('"used_diary_ud_id_seq"'::text)::regclass)
ud_d_id | integer | not null
ud_registration | character varying(12) |
ud_stock | character varying(7) |
ud_name | character varying(50) |
ud_required | date |
ud_rfl | character varying(25) |
ud_comments | text |
ud_created | timestamp with time zone | default now()
ud_completed | timestamp with time zone |
ud_u_id | integer | not null
ud_completed_by | integer |
ud_dd_id | integer | not null default 6
ud_authorized | timestamp without time zone |
ud_authorized_by | integer |
ud_tab | integer |
ud_tos_id | integer |
ud_debt | numeric(7,2) |
ud_m_id | integer |
ud_cc_id | character(2) |
ud_required_time | character varying(5) |
ud_tr_id | integer |
ud_pex_exists | boolean |
ud_pex_registration | character varying(12) |
ud_pex_make_model | character varying(40) |
ud_valet_instructions | text |
ud_valet_completed | timestamp without time zone |
ud_valet_completed_by | integer |
ud_pex_valet_completed | timestamp without time zone |
ud_pex_valet_completed_by | integer |
ud_pex_valet_option | integer |
ud_pex_valet_instructions | text |
ud_do_valet | boolean | default true
ud_valet_required | date |
ud_handover_date | date |
ud_phone_no | character varying(20) |
ud_valet_site | integer |
ud_ps_id | integer |
ud_partex_prep | text |
Indexes:
"used_diary_pkey" PRIMARY KEY, btree (ud_id)
"used_diary_completed_index" btree (ud_completed)
"used_diary_dealer_index" btree (ud_d_id)
"used_diary_dept_index" btree (ud_dd_id)
"used_diary_handover_date" btree (ud_handover_date)
"used_diary_ps_id" btree (ud_ps_id)
"used_diary_reg_index" btree (ud_registration)
"used_diary_required" btree (ud_required)
"used_diary_stock_index" btree (ud_stock)
"used_diary_ud_pex_valet_completed" btree (ud_pex_valet_completed)
"used_diary_ud_valet_completed" btree (ud_valet_completed)
"used_diary_valet_required" btree (ud_valet_required)
Foreign-key constraints:
"used_diary_ud_authorized_by_fkey" FOREIGN KEY (ud_authorized_by)
REFERENCE
S users(u_id)
"used_diary_ud_cc_id_fkey" FOREIGN KEY (ud_cc_id) REFERENCES
contract_codes
(cc_id)
"used_diary_ud_dd_id_fkey" FOREIGN KEY (ud_dd_id) REFERENCES
diary_departme
nts(dd_id)
"used_diary_ud_m_id_fkey" FOREIGN KEY (ud_m_id) REFERENCES
stock_makes(m_id
)
"used_diary_ud_pex_valet_completed_by_fkey" FOREIGN KEY
(ud_pex_valet_compl
eted_by) REFERENCES users(u_id)
"used_diary_ud_pex_valet_option_fkey" FOREIGN KEY (ud_pex_valet_option)
REF
ERENCES diary_valet_options(dv_id)
"used_diary_ud_ps_id_fkey" FOREIGN KEY (ud_ps_id) REFERENCES
partex_state(p
s_id)
"used_diary_ud_tab_fkey" FOREIGN KEY (ud_tab) REFERENCES tax_tabs(tt_id)
"used_diary_ud_tos_id_fkey" FOREIGN KEY (ud_tos_id) REFERENCES
type_of_sale
(tos_id)
"used_diary_ud_tr_id_fkey" FOREIGN KEY (ud_tr_id) REFERENCES
tax_rfl_values
(tr_id)
"used_diary_ud_valet_completed_by_fkey" FOREIGN KEY
(ud_valet_completed_by)
REFERENCES users(u_id)
"used_diary_ud_valet_site_fkey" FOREIGN KEY (ud_valet_site) REFERENCES
deal
erships(d_id)
Triggers:
"RI_ConstraintTrigger_110488" AFTER INSERT OR UPDATE ON used_diary FROM
dea
lerships NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_
FKey_check_ins"('<unnamed>', 'used_diary', 'dealerships', 'UNSPECIFIED', 'ud_d_
id', 'd_id')
"RI_ConstraintTrigger_110491" AFTER INSERT OR UPDATE ON used_diary FROM
use
rs NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_c
heck_ins"('<unnamed>', 'used_diary', 'users', 'UNSPECIFIED', 'ud_u_id', 'u_id')
"RI_ConstraintTrigger_110494" AFTER INSERT OR UPDATE ON used_diary FROM
use
rs NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_c
heck_ins"('<unnamed>', 'used_diary', 'users', 'UNSPECIFIED', 'ud_completed_by',
'u_id')
"RI_ConstraintTrigger_110501" AFTER DELETE ON used_diary FROM
used_diary_lo
g NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_no
action_del"('<unnamed>', 'used_diary_log', 'used_diary', 'UNSPECIFIED', 'ul_ud_
id', 'ud_id')
"RI_ConstraintTrigger_110502" AFTER UPDATE ON used_diary FROM
used_diary_lo
g NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_no
action_upd"('<unnamed>', 'used_diary_log', 'used_diary', 'UNSPECIFIED', 'ul_ud_
id', 'ud_id')
"RI_ConstraintTrigger_110504" AFTER DELETE ON used_diary FROM
used_comments
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noa
ction_del"('<unnamed>', 'used_comments', 'used_diary', 'UNSPECIFIED', 'uco_ud_i
d', 'ud_id')
"RI_ConstraintTrigger_110505" AFTER UPDATE ON used_diary FROM
used_comments
NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noa
ction_upd"('<unnamed>', 'used_comments', 'used_diary', 'UNSPECIFIED', 'uco_ud_i
d', 'ud_id')

goole=# explain update used_diary set ud_valet_completed=now(),
ud_valet_completed_by=25 where ud_valet_completed is null and
ud_valet_required < CURRENT_DATE-'7 days'::interval;
QUERY PLAN
-------------------------------------------------------------------------------------------
Bitmap Heap Scan on used_diary (cost=18.43..408.49 rows=585 width=318)
Recheck Cond: (ud_valet_required < (('now'::text)::date - '7
days'::interval))
Filter: (ud_valet_completed IS NULL)
-> Bitmap Index Scan on used_diary_valet_required (cost=0.00..18.43
rows=979 width=0)
Index Cond: (ud_valet_required < (('now'::text)::date - '7
days'::interval))
(5 rows)

--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000