Re: Quick select, slow update - help with performance problems - Mailing list pgsql-sql

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