Date: Mon, 13 Feb 2006 19:14:34 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Hill <ken@scottshill.com>
Cc: Postgres Help <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Message-ID: <20060214021434.GA75196@winnie.fuhr.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1139881725.3183.2.camel@localhost.localdomain>

On Mon, Feb 13, 2006 at 05:48:45PM -0800, Ken Hill wrote:
> When I try to analyze the query plan with:
> 
> EXPLAIN ANALYZE
> UPDATE ncccr10
> SET key = facilityno||'-'||
> 	lastname||'-'||
> 	sex||'-'||
> 	ssno||'-'||
> 	birthdate||'-'||
> 	primarysit||'-'||
> 	dxdate||'-'||
> 	morphology3
> WHERE date_part('year',dxdate) > '2000';
> 
> The query just never finishes (even 1 hour later). The colum key100 is
> indexed, and I'm setting the value of this
> column from other columns. Why is this so slow? 

If EXPLAIN ANALYZE is taking too long then could we at least see
the EXPLAIN output?  How many rows does the condition match?

SELECT count(*) FROM ncccr10 WHERE date_part('year',dxdate) > '2000';

Do you have an expression index on date_part('year',dxdate)?  Does
the table have any triggers or rules?  Have you queried pg_locks
to see if the update is blocked on an ungranted lock?

Do other tables have foreign key references to ncccr10?  If so then
you might need indexes on the referring columns.

What version of PostgreSQL are you running?

-- 
Michael Fuhr