From: Michael Fuhr <mike@fuhr.org>
To: Ken Hill <ken@scottshill.com>
Cc: Postgres Help <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Date: Mon, 13 Feb 2006 19:14:34 -0700
Message-ID: <20060214021434.GA75196@winnie.fuhr.org> (raw)
In-Reply-To: <1139881725.3183.2.camel@localhost.localdomain>
References: <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
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: <20060214021434.GA75196@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