On 29/01/14 11:00, Kevin Grittner wrote:
> James David Smith <james.david.smith@gmail.com> wrote:
>
>> Given the data is so large I don't want to be taking the data out
>> to a CSV or whatever and then loading it back in. I'd like to do
>> this within the database using SQL. I thought I would be able to
>> do this using a LOOP to be honest.
> I would be amazed if you couldn't do this with a single UPDATE
> statement. I've generally found declarative forms of such work to
> be at least one order of magnitude faster than going to either a PL
> or a script approach. I would start by putting together a SELECT
> query using window functions and maybe a CTE or two to list all the
> primary keys which need updating and the new values they should
> have. Once that SELECT was looking good, I would put it in the
> FROM clause of an UPDATE statement.
>
> That should work, but if you are updating a large percentage of the
> table, I would go one step further before running this against the
> production tables. I would put a LIMIT on the above-mentioned
> SELECT of something like 10000 rows, and script a loop that
> alternates between the UPDATE and a VACUUM ANALYZE on the table.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
James, you might consider dropping as many indexes on the table as you
safely can, and rebuilding them after the mass update. If you have lots
of such indexes, you will find this apprtoach to be a lot faster.
Cheers,
Gavin