Re: Slow update SQL

  <p><font size="2"><font face="Arial">You can try this too:<br /><br />...<br />WHERE EXTRACT(YEAR FROM dxdate::Date)
>2000</font></font><p><font size="2"><font face="Arial"><br /><br />Mauricio Fernández A.<br />Ingeniero de
Sistemas<br/>Universidad Autónoma de Manizales (Colombia)</font></font><p><font size="2"><font face="Arial"></font><br
/><br/>-----Mensaje original-----<br />De: pgsql-sql-owner@postgresql.org<br />[<a
href="mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@postgresql.org</a>]Ennombre de Bungsuputra Linan<br
/>Enviadoel: lunes, 27 febrero, 2006 3:42<br />Para: ken@scottshill.com; Postgres Help<br />Asunto: Re: [SQL] Slow
updateSQL<br /><br /><br />Hi Ken,<br /><br />I used to have the same problem. In my computer, using date_part in
WHERE<br/>clause will always slow down the system when the table has plenty of rows.<br /><br />My suggestion is try to
changethe query to:<br />... WHERE dxdate >= '2001-01-01';<br /><br />I hope this helps.<br /><br />Regards,<br
/>bungsu<br/><br />----- Original Message -----<br />From: Ken Hill<br />To: Postgres Help<br />Sent: Tuesday, February
14,2006 8:48 AM<br />Subject: [SQL] Slow update SQL<br /><br /><br />I'm experiencing a very slow query. The table
contains611,564 rows of data.<br />I vaccumed the table:<br /><br />VACUUM ANALYZE ncccr10;<br /><br />SELECT count(*)
FROMncccr10;<br />count<br />--------<br />611564<br />(1 row)<br /><br />When I try to analyze the query plan with:<br
/><br/>EXPLAIN ANALYZE<br />UPDATE ncccr10<br />SET key = facilityno||'-'||<br />lastname||'-'||<br />sex||'-'||<br
/>ssno||'-'||<br/>birthdate||'-'||<br />primarysit||'-'||<br />dxdate||'-'||<br />morphology3<br />WHERE
date_part('year',dxdate)> '2000';<br /><br />The query just never finishes (even 1 hour later). The colum key100
is<br/>indexed, and I'm setting the value of this<br />column from other columns. Why is this so slow?<br /><br /><br
/>---------------------------(endof broadcast)---------------------------<br />TIP 6: explain analyze is your friend<br
/></font>