Re: Slow update - index problem?

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