Re: Savepoints in transactions for speed?

On Thu, 2012-11-29 at 00:48 -0300, Claudio Freire wrote:
> Not really that fast if you have indices (and who doesn't have a PK or two).
>
> I've never been able to update (update) 2M rows in one transaction in
> reasonable times (read: less than several hours) without dropping
> indices. Doing it in batches is way faster if you can't drop the
> indices, and if you can leverage HOT updates.

I tried a quick test with 2M tuples and 3 indexes over int8, numeric,
and text (generated data). There was also an unindexed bytea column.
Using my laptop, a full update of the int8 column (which is indexed,
forcing cold updates) took less than 4 minutes.

I'm sure there are other issues with real-world workloads, and I know
that it's wasteful compared to something that can make use of HOT
updates. But unless there is something I'm missing, it's not really
worth the effort to batch if that is the size of the update.

Regards,
Jeff Davis