You can try this too:
...
WHERE
EXTRACT(YEAR FROM dxdate::Date) > 2000
Mauricio Fernández A.
Ingeniero de
Sistemas
Universidad Autónoma de Manizales (Colombia)
-----Mensaje
original-----
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]En
nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para:
[EMAIL PROTECTED]; Postgres Help
Asunto: Re: [SQL] Slow update
SQL
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?
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your
friend
