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@xxxxxxxx> 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 > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
