On 2009-04-13, Stuart McGraw <smcg2...@frii.com> wrote: > Jasen Betts wrote:
>> I see no reason to keep the index (and its associated UNIQUE >> constraint) during the update, AFAICT all it does is slow the process >> down. > > Thanks for the suggestion. > > Unfortunately I am doing this key renumbering in > an interactive gui app and as there are several million > rows involved, rebuilding indexes take too long. the update takes a long time too if it's updating all the rows. and updating the index piecewise at the same time. with the index extant I get from 20 (if the start and end ranges don't overlap) and 28s (with , to 28 seconds (maximum overlap) for a table with 1000000 (short) rows it takes 18 seconds if I first drop the index, then update, then restore the index. so, about the same amount of time or slightly faster by dropping the index. if wrap them in a transaction it takes 30s each way > I have continued searching for other ways to do this > but most of my google results are floating in a sea > of "update if insert fails, like mysql" results so > I still have a little hope it is possible. > I thought I remember seeing, a year or two ago, an > update statement with an ordered subquery that avoided > duplicate key errors but I am probably misrembering. maybe you can do it using a cursor? I've not looked at them yet. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
