Re: Updating a very large table

On Thu, Apr 23, 2009 at 4:06 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Rafael Domiciano <rafael(dot)domiciano(at)gmail(dot)com> 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 <Kevin(dot)Grittner(at)wicourts(dot)gov 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
>