Re: slow full table update

hi,

select count(*) from songs;
count
-------
54909
(1 row)

Time: 58.182 ms

update songs set views = 0;
UPDATE 54909
Time: 101907.837 ms
time is actually less than 10 minutes, but it is still very long :(

vacuum said>

VACUUM VERBOSE songs;
INFO: vacuuming "public.songs"
INFO: index "pk_songs2" now contains 54909 row versions in 595 pages
DETAIL: 193 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.06 sec.
INFO: index "fk_albums_aid_index" now contains 54909 row versions in 1330 pages
DETAIL: 193 index row versions were removed.
812 index pages have been deleted, 812 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.04 sec.
INFO: index "fk_artists_artid_index" now contains 54910 row versions in 628 pages
DETAIL: 193 index row versions were removed.
114 index pages have been deleted, 114 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.10 sec.
INFO: index "fk_users_uid_karaoke_index" now contains 54910 row versions in 2352 pages
DETAIL: 193 index row versions were removed.
2004 index pages have been deleted, 2004 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.95 sec.
INFO: index "datum_tag_indx" now contains 54910 row versions in 2083 pages
DETAIL: 193 index row versions were removed.
1728 index pages have been deleted, 1728 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.47 sec.
INFO: index "datum_video_indx" now contains 54910 row versions in 1261 pages
DETAIL: 193 index row versions were removed.
826 index pages have been deleted, 826 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.06 sec.
INFO: "songs": removed 193 row versions in 164 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: "songs": found 193 removable, 54909 nonremovable row versions in 6213 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 132969 unused item pointers.
0 pages are entirely empty.
CPU 0.07s/0.04u sec elapsed 1.74 sec.
INFO: vacuuming "pg_toast.pg_toast_28178"
INFO: index "pg_toast_28178_index" now contains 2700 row versions in 13 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_28178": found 0 removable, 2700 nonremovable row versions in 645 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 88 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
VACUUM
Time: 1750.460 ms

best regards
Marek Fiala
______________________________________________________________
> Od: scott(dot)marlowe(at)gmail(dot)com
> Komu: firerox(at)centrum(dot)cz
> CC: pgsql-performance(at)postgresql(dot)org
> Datum: 12.11.2008 21:55
> Předmět: Re: [PERFORM] slow full table update
>
>On Mon, Nov 10, 2008 at 9:30 AM, <firerox(at)centrum(dot)cz> wrote:
>> Hi,
>>
>> I have table with cca 60.000 rows and
>> when I run query as:
>> Update table SET column=0;
>> after 10 minutes i must stop query, but it still running :(
>
>What does
>
>vacuum verbose table;
>
>say? I'm wondering if it's gotten overly bloated.
>
>How long does
>
>select count(*) from table;
>
>take to run (use timing to time it)
>