From: | "Mauricio Fernandez A(dot)" <mfacontacto(at)ono(dot)com> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: Slow update SQL |
Date: | 2006-03-01 10:17:15 |
Message-ID: | CMEIKGCPKOPCGFKCNHLDCEKLCDAA.mfacontacto@ono.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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: pgsql-sql-owner(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]En nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para: ken(at)scottshill(dot)com; 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
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2006-03-01 11:33:01 | Re: Index to support LIKE '%suffix' queries |
Previous Message | Florian Weimer | 2006-03-01 07:19:40 | Re: Index to support LIKE '%suffix' queries |