From: Bungsuputra Linan <blinan@jogjamedianet.com>
To: ken@scottshill.com
To: Postgres Help <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Date: Mon, 27 Feb 2006 09:42:02 +0700
Message-ID: <040c01c63b47$66440b50$0200a8c0@server> (raw)
References: <1139881725.3183.2.camel@localhost.localdomain>
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?
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: blinan@jogjamedianet.com, ken@scottshill.com
Subject: Re: Slow update SQL
In-Reply-To: <040c01c63b47$66440b50$0200a8c0@server>
* 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