From: Kevin Grittner <kgrittn@ymail.com>
To: Florian Schröck <fschroeck@aycan.de>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Very slow update statement on 40mio rows
Date: Fri, 15 Feb 2013 06:59:17 -0800 (PST)
Message-ID: <1360940357.13532.YahooMailNeo@web162905.mail.bf1.yahoo.com> (raw)
In-Reply-To: <511E469F.70308@aycan.de>
References: <511E469F.70308@aycan.de>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@ymail.com, fschroeck@aycan.de
Subject: Re: Very slow update statement on 40mio rows
In-Reply-To: <1360940357.13532.YahooMailNeo@web162905.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox