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(at)copelandconsulting(dot)net>
Copeland Computer Consulting