Hi, thank you for your reply. Here is some aditional information: the problem is on every tables with small and large rows too. autovacuum is running. relpages reltuples 6213 54743 tables are almost write-only Munin Graphs shows that problems is with I/O bottleneck. I found out that Update 100 rows takes 0.3s but update 1000 rows takes 50s Is this better information? Thanks for any help. best regards Marek Fiala ______________________________________________________________ > Od: tv@xxxxxxxx > Komu: firerox@xxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > Datum: 10.11.2008 17:42 > Předmět: Re: slow full table update > >Sorry, but you have to provide much more information about the table. The >information you've provided is really not sufficient - the rows might be >large or small. I guess it's the second option, with a lots of dead rows. > >Try this: > >ANALYZE table; >SELECT relpages, reltuples FROM pg_class WHERE relname = 'table'; > >Anyway, is the autovacuum running? What are the parameters? Try to execute > >VACUUM table; > >and then run the two commands above. That might 'clean' the table and >improve the update performance. Don't forget each such UPDATE will >actually create a copy of all the modified rows (that's how PostgreSQL >works), so if you don't run VACUUM periodically or autovacuum demon, then >the table will bloat (occupy much more disk space than it should). > >If it does not help, try do determine if the UPDATE is CPU or disk bound. >I'd guess there are problems with I/O bottleneck (due to the bloating). > >regards >Tomas > >> 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 :( >> >> I've Postgres 8.1 with all default settings in postgres.conf >> >> Where is the problem? >> >> Thak you for any tips. >> >> best regards. >> Marek Fiala >> >> >> >> >> >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance >> > > > >-- >Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
