Re: Very slow update statement on 40mio rows - Mailing list pgsql-performance

Best regards

Florian Schröck

On Fri, Feb 15, 2013 at 9:32 AM, Florian Schröck <fschroeck@aycan.de> wrote:

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@aycan.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

if the number of rows you actually update is not very large relative
to size of the table, just for fun, try this:


CREATE OR REPLACE FUNCTION BakupFilesCandidateReset(BackupFiles)
RETURNS BOOL AS
$$ SELECT ($1).cStatus IN('NEW', 'WRITING', 'ONTAPE') AND   (($1).nTapeNr, ($1).nAFIOCounter, ($1).nBlockCounter,
($1).cStatus, ($1).bOnSetBlue, ($1).bOnSetYellow, ($1).nLastBackupTS)   IS DISTINCT FROM /* simple != will suffice if values are never null */  (0, 0, 0, 'NEW'::StatusT, false, false, '0001-01-01 00:00:00');
$$ LANGUAGE SQL IMMUTABLE;

CREATE INDEX ON BackupFiles(BakupFilesCandidateReset(BackupFiles)) WHERE BakupFilesCandidateReset(BackupFiles);


SELECT * FROM BackupFiles WHERE BakupFilesCandidateReset(BackupFiles);
UPDATE BackupFiles SET ... WHERE BakupFilesCandidateReset(BackupFiles);
etc

idea here is to maintain partial boolean index representing candidate
records to update.  plus it's nifty.   this is basic mechanism that
can be used as foundation for very fast push pull queues.

merlin