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