From: Robert Klemme <shortcutter@googlemail.com>
To: tv@fuzzy.cz
Cc: Harry Mantheakis <harry.mantheakis@riskcontrollimited.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Long Running Update - My Solution
Date: Mon, 27 Jun 2011 21:29:21 +0200
Message-ID: <BANLkTim++zcMMhQwQ77U4c3_QgXJL4Sapw@mail.gmail.com> (raw)
In-Reply-To: <df4df14087d064691f1a0a178adfe5dc.squirrel@sq.gransy.com>
References: <4E035649.20007@riskcontrollimited.com>
<4E089B6A.4000208@riskcontrollimited.com>
<df4df14087d064691f1a0a178adfe5dc.squirrel@sq.gransy.com>
On Mon, Jun 27, 2011 at 5:37 PM, <tv@fuzzy.cz> wrote:
>> The mystery remains, for me: why updating 100,000 records could complete
>> in as quickly as 5 seconds, whereas an attempt to update a million
>> records was still running after 25 minutes before we killed it?
>
> Hi, there's a lot of possible causes. Usually this is caused by a plan
> change - imagine for example that you need to sort a table and the amount
> of data just fits into work_mem, so that it can be sorted in memory. If
> you need to perform the same query with 10x the data, you'll have to sort
> the data on disk. Which is way slower, of course.
>
> And there are other such problems ...
I would rather assume it is one of the "other problems", typically
related to handling the TX (e.g. checkpoints, WAL, creating copies of
modified records and adjusting indexes...).
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
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: shortcutter@googlemail.com, tv@fuzzy.cz, harry.mantheakis@riskcontrollimited.com
Subject: Re: Long Running Update - My Solution
In-Reply-To: <BANLkTim++zcMMhQwQ77U4c3_QgXJL4Sapw@mail.gmail.com>
* 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