Re: slow full table update

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.marlowe@xxxxxxxxx
> Komu: firerox@xxxxxxxxxx
> CC: pgsql-performance@xxxxxxxxxxxxxx
> Datum: 12.11.2008 21:55
> Předmět: Re:  slow full table update
>
>On Mon, Nov 10, 2008 at 9:30 AM,  <firerox@xxxxxxxxxx> 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)
>


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux