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 E2C249DCCAA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 26 Feb 2006 22:49:14 -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 21077-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 26 Feb 2006 22:49:19 -0400 (AST)
X-Greylist: delayed 00:06:23.768366 by SQLgrey-
Received: from mail.jmn.net.id (mail.jmn.net.id [202.169.224.5])
	by postgresql.org (Postfix) with ESMTP id B3AFE9DCB62
	for <pgsql-sql@postgresql.org>; Sun, 26 Feb 2006 22:48:57 -0400 (AST)
Received: (qmail 20661 invoked by uid 510); 27 Feb 2006 09:42:15 +0700
Received: from 202.169.227.42 by semur.jmn.net.id (envelope-from
	<blinan@jogjamedianet.com>, uid 509) with qmail-scanner-D0N0
	(clamdscan: 0.87/1102. spamassassin: 3.0.4. perlscan: D0N0.  
	Clear:RC:1(202.169.227.42):.
	Processed in 0.079209 secs); 27 Feb 2006 02:42:15 -0000
TuKaNG-ScAn-JMN-Mail-From: blinan@jogjamedianet.com via semur.jmn.net.id
TuKaNG-ScAn-JMN: D0N0 (Clear:RC:1(202.169.227.42):. Processed in 0.079209 secs
	Process 20654)
Received: from host-202-169-227-42.jmn.net.id (HELO server) (202.169.227.42)
	by mail.jmn.net.id with SMTP; 27 Feb 2006 09:42:15 +0700
Message-ID: <040c01c63b47$66440b50$0200a8c0@server>
From: "Bungsuputra Linan" <blinan@jogjamedianet.com>
To: <ken@scottshill.com>,
	"Postgres Help" <pgsql-sql@postgresql.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
Subject: Re: Slow update SQL
Date: Mon, 27 Feb 2006 09:42:02 +0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200602/333
X-Sequence-Number: 24343

Hi Ken,

I used to have the same problem. In my computer, using date_part in WHERE
clause will always slow down the system when the table has plenty of rows.

My suggestion is try to change the query to:
... WHERE dxdate >= '2001-01-01';

I hope this helps.

Regards,
bungsu

----- Original Message -----
From: Ken Hill
To: Postgres Help
Sent: Tuesday, February 14, 2006 8:48 AM
Subject: [SQL] Slow update SQL


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?