Обсуждение: Quick select, slow update - help with performance problems

Hi folks.

My system is slowing down, more notably over the last few weeks.
The main reason is that it's on an old slow machine, and I'm in the process of 
sorting this.

However, I think that there are some issues within my database which I need to 
investigate.

There seems to be some performance issues with updating the database. I have a 
select, an insert  and an update based on the same table and condition.

The select:

select count(ud_id) from used_diary where ud_valet_completed is null and    ud_valet_required < CURRENT_DATE-'7
days'::interval;

completed in about a second. The insert

insert into used_diary_log (ul_u_id, ul_ud_id, ul_changes)select 25, ud_id, 'Valet automatically cleared down'     from
used_diary    where ud_valet_completed is null and         ud_valet_required < CURRENT_DATE-'7 days'::interval;
 

also completed in about a second. However the update 

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.

Could I have any suggestions on how I could start looking into why this is.  
Could it be the config of postgresql, issues with my schema, or something 
else?

Gary


-- 
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     

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?

--   Richard Huxton  Archonet Ltd

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
nullud_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 nullud_completed_by           | integer
         |ud_dd_id                  | integer                     | not null default 6ud_authorized             |
timestampwithout 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       |
charactervarying(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 trueud_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
INSERTOR 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"
AFTERDELETE 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_commentsNOT 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_commentsNOT 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