bungsu,<br /><br /> That does make the query work a bit faster. Thanks!<br /><br /> On Mon, 2006-02-27 at 09:42 +0700,
BungsuputraLinan wrote: <blockquote type="CITE"><pre>
<font color="#000000">Hi Ken,</font>
<font color="#000000">I used to have the same problem. In my computer, using date_part in WHERE</font>
<font color="#000000">clause will always slow down the system when the table has plenty of rows.</font>
<font color="#000000">My suggestion is try to change the query to:</font>
<font color="#000000">... WHERE dxdate >= '2001-01-01';</font>
<font color="#000000">I hope this helps.</font>
<font color="#000000">Regards,</font>
<font color="#000000">bungsu</font>
<font color="#000000">----- Original Message -----</font>
<font color="#000000">From: Ken Hill</font>
<font color="#000000">To: Postgres Help</font>
<font color="#000000">Sent: Tuesday, February 14, 2006 8:48 AM</font>
<font color="#000000">Subject: [SQL] Slow update SQL</font>
<font color="#000000">I'm experiencing a very slow query. The table contains 611,564 rows of data.</font>
<font color="#000000">I vaccumed the table:</font>
<font color="#000000">VACUUM ANALYZE ncccr10;</font>
<font color="#000000">SELECT count(*) FROM ncccr10;</font>
<font color="#000000">count</font>
<font color="#000000">--------</font>
<font color="#000000">611564</font>
<font color="#000000">(1 row)</font>
<font color="#000000">When I try to analyze the query plan with:</font>
<font color="#000000">EXPLAIN ANALYZE</font>
<font color="#000000">UPDATE ncccr10</font>
<font color="#000000">SET key = facilityno||'-'||</font>
<font color="#000000">lastname||'-'||</font>
<font color="#000000">sex||'-'||</font>
<font color="#000000">ssno||'-'||</font>
<font color="#000000">birthdate||'-'||</font>
<font color="#000000">primarysit||'-'||</font>
<font color="#000000">dxdate||'-'||</font>
<font color="#000000">morphology3</font>
<font color="#000000">WHERE date_part('year',dxdate) > '2000';</font>
<font color="#000000">The query just never finishes (even 1 hour later). The colum key100 is</font>
<font color="#000000">indexed, and I'm setting the value of this</font>
<font color="#000000">column from other columns. Why is this so slow?</font>
</pre></blockquote>
