From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: pgsql-performance@postgresql.org
To: Shawn <postgres@xmtservices.net>
Subject: Re: Slow Query
Date: Mon, 03 Sep 2007 13:07:41 -0500
Message-ID: <46DC071D.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <20070903091558.0780b963@boffin.xmtservices.net>
References: <20070901102947.0c0a50a3@boffin.xmtservices.net>
<46DA9522.EE98.0025.0@wicourts.gov>
<20070903091558.0780b963@boffin.xmtservices.net>
>>> On Mon, Sep 3, 2007 at 11:15 AM, in message
<20070903091558.0780b963@boffin.xmtservices.net>, Shawn
<postgres@xmtservices.net> wrote:
> On Sun, 02 Sep 2007 10:49:09 -0500 "Kevin Grittner"
> <Kevin.Grittner@wicourts.gov> wrote:
>
>> >>> On Sat, Sep 1, 2007 at 12:29 PM, in message
>> <20070901102947.0c0a50a3@boffin.xmtservices.net>, Shawn
>> <postgres@xmtservices.net> wrote:
>> > update shawns_data set alias = null;
>> > Even after VACUUM this simple line takes 35 sec to complete.
>>
>> Would any rows already have a null alias when you run this?
>> If so, try adding 'where alias is not null' to the query.
>
> 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.
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.
Did you ever capture the output of VACUUM VERBOSE against this table (as
Tom requested)?
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.)
What is the row count from the second update of the table in your script?
(An overly loose join there could bloat the table.)
-Kevin
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: Kevin.Grittner@wicourts.gov, postgres@xmtservices.net
Subject: Re: Slow Query
In-Reply-To: <46DC071D.EE98.0025.0@wicourts.gov>
* 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