From: Jeff Davis <pgsql@j-davis.com>
To: Claudio Freire <klaussfreire@gmail.com>
Cc: Mike Blackwell <mike.blackwell@rrd.com>
Cc: postgres performance list <pgsql-performance@postgresql.org>
Subject: Re: Savepoints in transactions for speed?
Date: Thu, 29 Nov 2012 10:32:22 -0800
Message-ID: <1354213942.10198.208.camel@jdavis-laptop> (raw)
In-Reply-To: <CAGTBQpa5KzE1C4TaRDvaEwiLoO800brWOzJH_t8diEH7trq6Ww@mail.gmail.com>
References: <CANPAkgs_LvZV-fabTNN-ahiVcwSBAyOnArw1v+QbCePg7on6ZA@mail.gmail.com>
<1354145331.1766.84.camel@sussancws0025>
<CAGTBQpa5KzE1C4TaRDvaEwiLoO800brWOzJH_t8diEH7trq6Ww@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgsql@j-davis.com, klaussfreire@gmail.com, mike.blackwell@rrd.com
Subject: Re: Savepoints in transactions for speed?
In-Reply-To: <1354213942.10198.208.camel@jdavis-laptop>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox