Re: slow full table update

От:
Тема: Re: slow full table update
Дата: ,
Msg-id: 200811122347.21602@centrum.cz
(см: обсуждение, исходный текст)
Ответ на: Re: slow full table update  ("Scott Marlowe")
Ответы: Re: slow full table update  ("Scott Marlowe")
Re: slow full table update  (PFC)
Список: pgsql-performance

Скрыть дерево обсуждения

slow full table update  (<>, )
 Re: slow full table update  (, )
  Re: slow full table update  (<>, )
   Re: slow full table update  (, )
    Re: slow full table update  (<>, )
     Re: slow full table update  (Richard Huxton, )
      Re: slow full table update  ("Vladimir Sitnikov", )
      Re: slow full table update  (Tom Lane, )
       Re: slow full table update  (Tomas Vondra, )
 Re: slow full table update  ("Scott Marlowe", )
  Re: slow full table update  (<>, )
   Re: slow full table update  ("Scott Marlowe", )
    Re: slow full table update  (Tomas Vondra, )
   Re: slow full table update  (PFC, )

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: 
> Komu: 
> CC: 
> Datum: 12.11.2008 21:55
> Předmět: Re: [PERFORM] slow full table update
>
>On Mon, Nov 10, 2008 at 9:30 AM,  <> 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)
>



В списке pgsql-performance по дате сообщения:

От: "Scott Marlowe"
Дата:
Сообщение: Re: Performance Question
От: "Dave Page"
Дата:
Сообщение: Re: Performance Question