Skip site navigation (1) Skip section navigation (2)

Re: slow full table update

From: <firerox(at)centrum(dot)cz>
To: <scott(dot)marlowe(at)gmail(dot)com>,"pgsql-performance" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: slow full table update
Date: 2008-11-12 22:47:20
Message-ID: 200811122347.21602@centrum.cz (view raw or flat)
Thread:
Lists: pgsql-performance
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
&gt; 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)
>


In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2008-11-12 22:58:49
Subject: Re: slow full table update
Previous:From: Tom LaneDate: 2008-11-12 20:57:38
Subject: Re: Performance Question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group