Re: Slow update SQL

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