Re: Very slow update statement on 40mio rows

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öck <fschroeck(at)aycan(dot)de> wrote:
>
>> UPDATE BackupFiles
>> SET nTapeNr=0, nAFIOCounter=0, nBlockCounter=0,
>> cStatus='NEW'::StatusT, bOnSetBlue=false,
>> bOnSetYellow=false, nLastBackupTS= '0001-01-01 00:00:00'
>> WHERE cStatus='NEW' OR cStatus='WRITING' OR cStatus='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=0, nAFIOCounter=0, nBlockCounter=0,
> cStatus='NEW'::StatusT, bOnSetBlue=false,
> bOnSetYellow=false, nLastBackupTS= '0001-01-01 00:00:00'
> WHERE (cStatus='NEW' OR cStatus='WRITING' OR cStatus='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
>