Slow update - index problem?

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