Thread: Slow update - index problem?
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
Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
> 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.
It kinda sounds like you've done a whole lot of updating and never
vacuumed this table. What does 'VACUUM FULL VERBOSE ind' tell you?
> Any ideas? Would *removing* indexes make it faster?
Yes, but I doubt that it's the indexes that are the bulk of the problem.
regards, tom lane
On Fri, 21 Feb 2003, Tom Lane wrote:
>Christopher Murtagh <christopher.murtagh@mcgill.ca> writes:
>> 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.
>
>It kinda sounds like you've done a whole lot of updating and never
>vacuumed this table. What does 'VACUUM FULL VERBOSE ind' tell you?
INFO: --Relation public.ind--
INFO: Pages 1980: Changed 64, reaped 1898, Empty 0, New 0; Tup 19868: Vac
11909, Keep/VTL 0/0, UnUsed 71516, MinLen 102, MaxLen 246; Re-using:
Free/Avail. Space 12642596/12629232; EndEmpty/Avail. Pages 0/1722.
CPU 0.03s/0.00u sec elapsed 0.04 sec.
INFO: Index ind_pkey: Pages 435; Tuples 19868: Deleted 11909.
CPU 0.01s/0.07u sec elapsed 2.96 sec.
INFO: Index ind_fnm_idx: Pages 503; Tuples 19868: Deleted 11909.
CPU 0.02s/0.06u sec elapsed 3.98 sec.
INFO: Index ind_lnm_idx: Pages 492; Tuples 19868: Deleted 11909.
CPU 0.01s/0.06u sec elapsed 3.44 sec.
INFO: Index ind_ind_id_idx: Pages 315; Tuples 19868: Deleted 11909.
CPU 0.03s/0.08u sec elapsed 3.03 sec.
INFO: Index ind_ord_idx: Pages 677; Tuples 19868: Deleted 11909.
CPU 0.04s/0.12u sec elapsed 5.67 sec.
INFO: Index ind_stf_id_key: Pages 563; Tuples 19868: Deleted 11909.
CPU 0.03s/0.10u sec elapsed 4.81 sec.
INFO: Index ind_std_id_key: Pages 562; Tuples 19868: Deleted 11909.
CPU 0.01s/0.05u sec elapsed 0.49 sec.
.... then it just hangs (and blocks all connections to the db - I assume
this is a table lock while it updates), so I had to stop it. We have done
vacuum analyze on the entire DB several times today, and they finished
fairly well.
Should I try this again when I can shut down the DB?
Cheers,
Chris
--
Christopher Murtagh
Webmaster / Sysadmin
Web Communications Group
McGill University
Montreal, Quebec
Canada
Tel.: (514) 398-3122
Fax: (514) 398-2017
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