Re: Updating a very large table

On Thu, Apr 23, 2009 at 11:21 PM, Michael Monnerie
<michael(dot)monnerie(at)is(dot)it-management(dot)at> wrote:
> On Freitag 24 April 2009 Rafael Domiciano wrote:
>> this table has about 15 indexes...
>>
>> How good are to Cluster table? Has any criteria to cluster table? How
>> can I do it?
>
> http://www.postgresql.org/docs/8.3/interactive/sql-cluster.html
> CLUSTER tablename [ USING indexname after which you want data to be
> sorted ]
>
> Postgres then rewrites the whole table, which creates a new file on the
> disk with the table entries written in the order of the index you chose.
> Don't forget the ANALYZE afterwards.

Note that cluster on a table with random ordering can be really slow.
I've found it's much faster to reorder to a temp table then truncate
the original and insert into ... select from temptable order by xyz
to refill it. Since either cluster or truncate select order by are
both gonna lock the table from users, you might as well use what's
faster for you.

> Question: This will not rewrite the indices for that table, right? Then
> a REINDEX could be interesting too.

Also, if you're doing the select into thing, you can drop the indexes
then recreate them. Usually also faster on a big table being
reordered.