Slow update - index problem?

From: Christopher Murtagh <christopher(dot)murtagh(at)mcgill(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Slow update - index problem?
Date: 2003-02-21 16:07:21
Message-ID: Pine.LNX.4.44.0302211103360.17067-100000@blues.wcg.mcgill.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

--

Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada

Tel.: (514) 398-3122
Fax: (514) 398-2017

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Eric B.Ridge 2003-02-21 16:11:48 Re: Fwd: REWRITE_INVOKE_MAX and "query may contain cycles"
Previous Message Henrik Steffen 2003-02-21 15:46:35 Re: reindex vs. drop index , create index