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 -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
