Re: Slow update - index problem?

Considering that tgl_upd doesn't appear to be indexed, I wouldn't expect
the indexes to even be an issue here.

I completely agree with Tom that it seems like a "VACUUM FULL VERBOSE"
is in order.  If you're still confused as to what is going on, doing a,
"EXPLAIN ANALYZE update ind set tgl_upd = 'f';", may also help give a
better picture.  I fully expect it to say something like, "Seq Scan on
ind...".


Regards,

	Greg Copeland


On Fri, 2003-02-21 at 10:07, Christopher Murtagh wrote:
>  I'm trying to perform a very simple update that is very slow. I suspect 
> that it is a key/index problem, but we've tried a number of things and 
> we're stumped. The table is:
> 
>  Column  |          Type          |               Modifiers
> ---------+------------------------+---------------------------------------
>  ind_id  | integer                | default nextval('"ind_id_seq"'::text)
>  bnr_id  | integer                | not null
>  jim_id  | integer                |
>  fnm     | character varying(40)  |
>  nnm     | character varying(40)  |
>  mnm     | character varying(40)  |
>  mdn     | character varying(40)  |
>  lnm     | character varying(40)  |
>  cnm     | character varying(200) |
>  ord     | character varying(80)  |
>  eml     | character varying(80)  |
>  stf_id  | character varying(9)   |
>  std_id  | character varying(9)   |
>  tgl_upd | boolean                |
> Indexes: ind_pkey primary key btree (bnr_id),
>          ind_std_id_key unique btree (std_id),
>          ind_stf_id_key unique btree (stf_id),
>          ind_fnm_idx btree (fnm),
>          ind_ind_id_idx btree (ind_id),
>          ind_lnm_idx btree (lnm),
>          ind_ord_idx btree (ord)
> 
> and the query:
> 
> update ind set tgl_upd = 'f';
> 
>  The table has 19867 records, and this query takes over 20 minutes. When I 
> export the data to a new DB, with the same table (no indexes) it takes 
> about 3 seconds.
> 
>  Any ideas? Would *removing* indexes make it faster? If so, this sounds 
> strange. Any help/info would be much appreciated.
> 
> Cheers,
> 
> Chris
-- 
Greg Copeland <greg@copelandconsulting.net>
Copeland Computer Consulting