X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 7BDE99DCC4F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 21:48:08 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 78398-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 21:48:12 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82])
	by postgresql.org (Postfix) with ESMTP id 5EE839DCC53
	for <pgsql-sql@postgresql.org>; Mon, 13 Feb 2006 21:48:06 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc12) with SMTP
	id <20060214014809m1200a9lche>; Tue, 14 Feb 2006 01:48:09 +0000
Subject: Slow update SQL
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Postgres Help <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary="=-VUIOsE4b7Z+ozBTktk0L"
Organization: Scotts Hill Associates, Inc.
Date: Mon, 13 Feb 2006 17:48:45 -0800
Message-Id: <1139881725.3183.2.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.078 required=5 tests=[AWL=0.077,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.078
X-Spam-Level: 
X-Archive-Number: 200602/144
X-Sequence-Number: 24154


--=-VUIOsE4b7Z+ozBTktk0L
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

I'm experiencing a very slow query. The table contains 611,564 rows of
data. I vaccumed the table:

VACUUM ANALYZE ncccr10;

SELECT count(*) FROM ncccr10;
 count
--------
 611564
(1 row)

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? 


--=-VUIOsE4b7Z+ozBTktk0L
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table:<BR>
<BR>
VACUUM ANALYZE ncccr10;<BR>
<BR>
SELECT count(*) FROM ncccr10;<BR>
 count<BR>
--------<BR>
 611564<BR>
(1 row)<BR>
<BR>
When I try to analyze the query plan with:<BR>
<BR>
EXPLAIN ANALYZE<BR>
UPDATE ncccr10<BR>
SET key = facilityno||'-'||<BR>
	lastname||'-'||<BR>
	sex||'-'||<BR>
	ssno||'-'||<BR>
	birthdate||'-'||<BR>
	primarysit||'-'||<BR>
	dxdate||'-'||<BR>
	morphology3<BR>
WHERE date_part('year',dxdate) &gt; '2000';<BR>
<BR>
The query just never finishes (even 1 hour later). The colum key100 is indexed, and I'm setting the value of this<BR>
column from other columns. Why is this so slow? <BR>
<BR>
</BODY>
</HTML>

--=-VUIOsE4b7Z+ozBTktk0L--