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

Re: Updating a very large table

From: Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: Chris Browne <cbbrowne(at)acm(dot)org>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Updating a very large table
Date: 2009-04-25 05:26:29
Message-ID: 49F29F05.30805@cheapcomplexdevices.com (view raw or flat)
Thread:
Lists: pgsql-admin
Kevin Grittner wrote:
> Chris Browne <cbbrowne(at)acm(dot)org> wrote: 
>  
>> I'd suggest adding an index
>  
> The OP said the table had 15 indexes already.  I would guess one of
> those could be used.  Perhaps it has a primary key....
>  
>>   update table1 set new_column = [whatever calculation]
>>   where new_column is null and
>>         quasi_unique_column in
>>           (select quasi_unique_column from table1
>>            where new_column is null limit 1000);
>  
> Or, if the primary key (or other unique or quasi-unique existing
> index) has multiple columns, this could still be done with:
>  
>   update table1 set new_column = [whatever calculation]
>   where new_column is null and
>         (col1, col2) in
>           (select col1, col2 from table1
>            where new_column is null limit 1000);
>  

Would doing something with ctid be even better?
Or does it have some risks I'm missing.   I'm thinking
something like:

fli=# select max(ctid) from table1;
     max
-------------
 (183000,42)
(1 row)

Then

update table set new_column=[whatever] where ctid<'(10000,1)';
vacuum;
update table set new_column=[whatever] where ctid>'(10000,1)' and ctid<'(20000,1');
vacuum;
...
update table set new_column=[whatever] where ctid>'(180000,1)';
vacuum;

and perhaps a final

update table set new_column=[whatever] where new_column is null;

to catch any this might have missed?


Seems this makes it easer to control how much the table will
bloat too -- if I only want it to bloat 5% I divide max(ctid) by 20
for each group size....


In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2009-04-25 13:59:39
Subject: Re: Updating a very large table
Previous:From: Dot YetDate: 2009-04-25 04:20:08
Subject: Re: How to map columns in pg_stat_activity to windows PID

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