Message-Id: <46DC071D.EE98.0025.0@wicourts.gov>
Date: Mon, 03 Sep 2007 13:07:41 -0500
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: <pgsql-performance@postgresql.org>,
	"Shawn" <postgres@xmtservices.net>
Subject: Re: Slow Query
References: <20070901102947.0c0a50a3@boffin.xmtservices.net>
	<46DA9522.EE98.0025.0@wicourts.gov>
	<20070903091558.0780b963@boffin.xmtservices.net>
In-Reply-To: <20070903091558.0780b963@boffin.xmtservices.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

>>> On Mon, Sep 3, 2007 at 11:15 AM, in message
<20070903091558.0780b963@boffin.xmtservices.net>, Shawn
<postgres@xmtservices.net> wrote:=20
>  On Sun, 02 Sep 2007 10:49:09 -0500 "Kevin Grittner"
> <Kevin.Grittner@wicourts.gov> wrote:
>=20
>> >>> On Sat, Sep 1, 2007 at 12:29 PM, in message
>> <20070901102947.0c0a50a3@boffin.xmtservices.net>, Shawn
>> <postgres@xmtservices.net> wrote:=20
>> > update shawns_data set alias =3D null;
>> > Even after VACUUM this simple line takes 35 sec to complete.
>> =20
>> Would any rows already have a null alias when you run this?
>> If so, try adding 'where alias is not null' to the query.
>=20
> This one initially added about 10sec to the run but I added a HASH
> index on the alias field and its now about 5 sec average runtime, a net
> improvement.
=20
Testing for null on 15,700 rows took five seconds more than the time saved
from not updating some portion of the rows?????  I've never seen anything
remotely like that.
=20
Did you ever capture the output of VACUUM VERBOSE against this table (as
Tom requested)?
=20
What happens if you run CLUSTER against this table before running one of
these updates?  (Be sure to do that VACUUM VERBOSE first, to see what the
"old" state of the table was, and run it again after.)
=20
What is the row count from the second update of the table in your script?
(An overly loose join there could bloat the table.)
=20
-Kevin
=20