Message-ID: <511E54FA.8010107@aycan.de>
Date: Fri, 15 Feb 2013 16:32:10 +0100
From: =?ISO-8859-1?Q?Florian_Schr=F6ck?= <fschroeck@aycan.de>
Organization: aycan Digitalsysteme GmbH
User-Agent: Mozilla/5.0 (X11; Linux i686;
	rv:17.0) Gecko/20130106 Thunderbird/17.0.2
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Very slow update statement on 40mio rows
References: <511E469F.70308@aycan.de>
	<1360940357.13532.YahooMailNeo@web162905.mail.bf1.yahoo.com>
In-Reply-To: <1360940357.13532.YahooMailNeo@web162905.mail.bf1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hello Kevin,
not updating every row which doesn't need the update solved the problem!
Your query took only 1 minute. :)

Thank you so much for the fast response, have a great weekend!

PS: When you switch to "TEXT" on the explain URL you can see the final
runtime which was 66 minutes with the original statement.

Best regards,
Florian

On 02/15/2013 03:59 PM, Kevin Grittner wrote:
> Florian Schr=F6ck <fschroeck@aycan.de> wrote:
>
>> UPDATE BackupFiles
>>    SET nTapeNr=3D0, nAFIOCounter=3D0, nBlockCounter=3D0,
>>        cStatus=3D'NEW'::StatusT, bOnSetBlue=3Dfalse,
>>        bOnSetYellow=3Dfalse, nLastBackupTS=3D '0001-01-01 00:00:00'
>>    WHERE cStatus=3D'NEW' OR cStatus=3D'WRITING' OR cStatus=3D'ONTAPE';
>>
>> Explain analyze: http://explain.depesz.com/s/8y5
>> The statement takes 60-90 minutes.
> The EXPLAIN ANALYZE at that URL shows a runtime of 3 minutes and 41
> seconds.
>
>> I tried to optimize the settings but until now without success.
>>
>> Can we optimize this update statement somehow? Do you have any
>> other ideas?
> Are there any rows which would already have the values that you are
> setting?  If so, it would be faster to skip those by using this
> query:
>
> UPDATE BackupFiles
>   SET nTapeNr=3D0, nAFIOCounter=3D0, nBlockCounter=3D0,
>       cStatus=3D'NEW'::StatusT, bOnSetBlue=3Dfalse,
>       bOnSetYellow=3Dfalse, nLastBackupTS=3D '0001-01-01 00:00:00'
>   WHERE (cStatus=3D'NEW' OR cStatus=3D'WRITING' OR cStatus=3D'ONTAPE')
>     AND (nTapeNr <> 0 OR nAFIOCounter <> 0 OR nBlockCounter <> 0
>          OR cStatus <> 'NEW'::StatusT
>          OR bOnSetBlue IS DISTINCT FROM false
>          OR bOnSetYellow IS DISTINCT FROM false
>          OR nLastBackupTS <> '0001-01-01 00:00:00');
>
> Another way to accomplish this is with the
> suppress_redundant_updates_trigger() trigger function:
>
> http://www.postgresql.org/docs/9.2/interactive/functions-trigger.html
>


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance