Re: Slow update SQL

[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