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 14A189DCB72 for ; Tue, 28 Feb 2006 21:26:28 -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 58748-04 for ; Tue, 28 Feb 2006 21:26:31 -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 92DBC9DCB0D for ; Tue, 28 Feb 2006 21:26:25 -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 <20060301012627m12001co7ee>; Wed, 1 Mar 2006 01:26:28 +0000 Subject: Re: Slow update SQL From: Ken Hill Reply-To: ken@scottshill.com To: Bungsuputra Linan Cc: Postgres Help In-Reply-To: <040c01c63b47$66440b50$0200a8c0@server> References: <1139881725.3183.2.camel@localhost.localdomain> <040c01c63b47$66440b50$0200a8c0@server> Content-Type: multipart/alternative; boundary="=-plZ5tDgLwx0ryR1unYhh" Organization: Scotts Hill Associates, Inc. Date: Tue, 28 Feb 2006 17:26:31 -0800 Message-Id: <1141176391.27050.0.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.093 required=5 tests=[AWL=0.092, HTML_MESSAGE=0.001] X-Spam-Score: 0.093 X-Spam-Level: X-Archive-Number: 200602/328 X-Sequence-Number: 24338 --=-plZ5tDgLwx0ryR1unYhh Content-Type: text/plain Content-Transfer-Encoding: 7bit bungsu, That does make the query work a bit faster. Thanks! On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote: > 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? > --=-plZ5tDgLwx0ryR1unYhh Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit bungsu,

That does make the query work a bit faster. Thanks!

On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote:

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?

--=-plZ5tDgLwx0ryR1unYhh--