Thread
-
Slow update - index problem?
Christopher Murtagh <christopher.murtagh@mcgill.ca> — 2003-02-21T16:07:21Z
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 -
Re: Slow update - index problem?
Tom Lane <tgl@sss.pgh.pa.us> — 2003-02-21T16:17:11Z
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
-
Re: Slow update - index problem?
Christopher Murtagh <christopher.murtagh@mcgill.ca> — 2003-02-21T16:27:41Z
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 -
Re: Slow update - index problem?
Greg Copeland <greg@copelandconsulting.net> — 2003-02-21T19:00:48Z
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