From: Michael Fuhr <mike@fuhr.org>
To: Ken Hill <ken@scottshill.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Slow update SQL
Date: Mon, 13 Feb 2006 22:17:21 -0700
Message-ID: <20060214051721.GA76364@winnie.fuhr.org> (raw)
In-Reply-To: <1139885286.3183.10.camel@localhost.localdomain>
References: <1139881725.3183.2.camel@localhost.localdomain>
<20060214021434.GA75196@winnie.fuhr.org>
<1139885286.3183.10.camel@localhost.localdomain>
[Please copy the mailing list on replies.]
On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
> count
> --------
> 199209
> (1 row)
You're updating about a third of the table; an expression index on
date_part probably wouldn't help because the planner is likely to
stick with a sequential scan for such a large update. Even if it
did help it's likely to be a small fraction of the total time.
The table definition you sent me showed nine indexes. You might
see a substantial performance improvement by dropping all the
indexes, doing the update, then creating the indexes again (don't
forget to vacuum and analyze the table after the update). However,
dropping the indexes has obvious implications for other queries so
you might need to do the update at a time when that doesn't matter.
> > Have you queried pg_locks
> > to see if the update is blocked on an ungranted lock?
>
> I don't know what that is. How do I query pg_locks?
SELECT * FROM pg_locks;
http://www.postgresql.org/docs/7.4/static/monitoring-locks.html> > What version of PostgreSQL are you running?
>
> 7.4.8. Thank you for your help.
Newer versions generally perform better; consider upgrading to 8.0
or 8.1 if possible.
--
Michael Fuhr
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-sql@postgresql.org
Cc: mike@fuhr.org, ken@scottshill.com
Subject: Re: Slow update SQL
In-Reply-To: <20060214051721.GA76364@winnie.fuhr.org>
* 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