On Thu, Apr 23, 2009 at 4:06 PM, Kevin Grittner <[email protected] > wrote:
> Rafael Domiciano <[email protected]> wrote: > > > Doing the alter table to add the new column was fast: ALTER TABLE > > table1 ADD COLUMN new_column date; > > So far, so good. > > > The problem is that I have to do a update in this column, and the > > values are going to be the a misc of others 2 columns of the table1, > > something like this: > > > > update table1 > > set new_column = (date) > > where > > new_column is null; > > You're probably going to want to do that in small chunks (I would try > to avoid updating more than about 10,000 rows per transaction.) Yeah, we are trying this. Thnks. > > > > Postgres Version: 8.3.6 > > You should update to the latest 8.3 bug-fix version, if you can. No > conversion needed; just stop on the old software and start on the new. > > > Os.: Fedora Core 9 > > 4 Gb Ram > > I assume that you've tuned PostgreSQL, but if my other suggestions > don't help, please post again with the non-commented lines of the > postgresql.conf file, and actual table specifications and query text, > along with an EXPLAIN of the query. > Yes, I've tuned configuration to fit the hardware, and it's ok. > > > Kevin Grittner <[email protected] wrote: > >> What does a VACUUM ANALYZE VERBOSE on this table show as output? > > > > INFO: "table1": encontrados 1572 versões de registros removíveis e > > 8022357 não-removíveis em 244388 páginas > > DETAIL: 7101 versões de registros não vigentes não podem ser > > removidas ainda. > > Havia 1657653 ponteiros de itens não utilizados. > > 62515 páginas contém espaço livre útil. > > 0 páginas estão completamente vazias. > > CPU 9.38s/26.74u sec elapsed 27540.53 sec. > > That could be better. You might want to schedule an overnight CLUSTER > of the table (followed by an ANALYZE) before attempting the update. > > You don't show any index or toast-table information. Was there none? this table has about 15 indexes... How good are to Cluster table? Has any criteria to cluster table? How can I do it? > > > >> What are the last few lines of VACUUM ANALYZE VERBOSE on the whole > >> database? > > > > INFO: mapeamento de espaço livre contém 152886 páginas em 907 > > relações > > DETAIL: O total de 151280 páginas estão em uso (incluindo excesso). > > 151280 páginas são necessárias para encontrar todo espaço livre. > > Limites atuais são: 153600 páginas, 2500 relações, utilizando 1065 > > kB. > > That looks OK, although you're right at the edge. > > -Kevin >
