DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
	h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=Q+7FhoTgldCXLEfp3UM4r8VN8YigQVku3/jlobTbo+lXYZ9N/6QHzbQXAH9pk41Lb5l3dNWljfYvqLzuvNK5HWLdnoNooz48HTiNdHZt+yDxM2oiqW+lPKJk7hZAPskj3KwO+68pYl4tQpIx9PvVO+d9hSd4Bw9zyZQqRiyH9cc=;
References: <511E469F.70308@aycan.de>
Message-ID: <1360940357.13532.YahooMailNeo@web162905.mail.bf1.yahoo.com>
Date: Fri, 15 Feb 2013 06:59:17 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Very slow update statement on 40mio rows
To: =?iso-8859-1?Q?Florian_Schr=F6ck?= <fschroeck@aycan.de>,
	"pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: <511E469F.70308@aycan.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Florian Schr=F6ck <fschroeck@aycan.de> wrote:

> UPDATE BackupFiles
>=A0=A0 SET nTapeNr=3D0, nAFIOCounter=3D0, nBlockCounter=3D0,
>=A0=A0=A0=A0=A0=A0 cStatus=3D'NEW'::StatusT, bOnSetBlue=3Dfalse,
>=A0=A0=A0=A0=A0=A0 bOnSetYellow=3Dfalse, nLastBackupTS=3D '0001-01-01 00:0=
0:00'
>=A0=A0 WHERE cStatus=3D'NEW' OR cStatus=3D'WRITING' OR cStatus=3D'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?=A0 If so, it would be faster to skip those by using this
query:

UPDATE BackupFiles
=A0 SET nTapeNr=3D0, nAFIOCounter=3D0, nBlockCounter=3D0,
=A0=A0=A0=A0=A0 cStatus=3D'NEW'::StatusT, bOnSetBlue=3Dfalse,
=A0=A0=A0=A0=A0 bOnSetYellow=3Dfalse, nLastBackupTS=3D '0001-01-01 00:00:00'
=A0 WHERE (cStatus=3D'NEW' OR cStatus=3D'WRITING' OR cStatus=3D'ONTAPE')
=A0=A0=A0 AND (nTapeNr <> 0 OR nAFIOCounter <> 0 OR nBlockCounter <> 0
=A0=A0=A0=A0=A0=A0=A0=A0 OR cStatus <> 'NEW'::StatusT
=A0=A0=A0=A0=A0=A0=A0=A0 OR bOnSetBlue IS DISTINCT FROM false
=A0=A0=A0=A0=A0=A0=A0=A0 OR bOnSetYellow IS DISTINCT FROM false
=A0=A0=A0=A0=A0=A0=A0=A0 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

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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance