From: Ken Hill <ken@scottshill.com>
To: Bungsuputra Linan <blinan@jogjamedianet.com>
Cc: Postgres Help <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Date: Tue, 28 Feb 2006 17:26:31 -0800
Message-ID: <1141176391.27050.0.camel@localhost.localdomain> (raw)
In-Reply-To: <040c01c63b47$66440b50$0200a8c0@server>
References: <1139881725.3183.2.camel@localhost.localdomain>
<040c01c63b47$66440b50$0200a8c0@server>
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?
>
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: ken@scottshill.com, blinan@jogjamedianet.com
Subject: Re: Slow update SQL
In-Reply-To: <1141176391.27050.0.camel@localhost.localdomain>
* 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