Skip site navigation (1) Skip section navigation (2)

Re: Updating a very large table

From: Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
To: Michael Monnerie <michael(dot)monnerie(at)is(dot)it-management(dot)at>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Updating a very large table
Date: 2009-04-24 06:07:04
Message-ID: dcc563d10904232307n616b4e25ve42632855a95f35@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
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.

In response to

Responses

pgsql-admin by date

Next:From: Simon RiggsDate: 2009-04-24 08:46:54
Subject: Re: postgres 8.2.9 can't drop database in single user mode
Previous:From: Michael MonnerieDate: 2009-04-24 05:21:43
Subject: Re: Updating a very large table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group