Re: Very slow update statement on 40mio rows

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

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company