X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 3E45E9DCCD0
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Mar 2006 06:22:45 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 49860-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed,  1 Mar 2006 06:22:46 -0400 (AST)
X-Greylist: delayed 00:05:32.301361 by SQLgrey-
Received: from resmta03.ono.com (smtp.ono.com [62.42.230.12])
	by postgresql.org (Postfix) with ESMTP id C68DB9DCCDB
	for <pgsql-sql@postgresql.org>; Wed,  1 Mar 2006 06:22:42 -0400 (AST)
Received: from Zeus (84.120.16.81) by resmta03.ono.com (7.2.052)
	id 43C1E4D40047CFF1 for pgsql-sql@postgresql.org;
	Wed, 1 Mar 2006 11:17:11 +0100
From: "Mauricio Fernandez A." <mfacontacto@ono.com>
To: <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Date: Wed, 1 Mar 2006 11:17:15 +0100
Message-ID: <CMEIKGCPKOPCGFKCNHLDCEKLCDAA.mfacontacto@ono.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002A_01C63D21.B1F4BFA0"
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Importance: Normal
In-Reply-To: <040c01c63b47$66440b50$0200a8c0@server>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.559 required=5 tests=[AWL=-0.360,
	DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_POST=1.44, HTML_MESSAGE=0.001]
X-Spam-Score: 1.559
X-Spam-Level: *
X-Archive-Number: 200603/2
X-Sequence-Number: 24348

This is a multi-part message in MIME format.

------=_NextPart_000_002A_01C63D21.B1F4BFA0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

You can try this too:

...
WHERE EXTRACT(YEAR FROM dxdate::Date) > 2000



Mauricio Fern=C3=A1ndez A.
Ingeniero de Sistemas
Universidad Aut=C3=B3noma de Manizales (Colombia)



-----Mensaje original-----
De: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]En nombre de Bungsuputra Linan
Enviado el: lunes, 27 febrero, 2006 3:42
Para: ken@scottshill.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 >=3D '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 =3D 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


------=_NextPart_000_002A_01C63D21.B1F4BFA0
Content-Type: text/html;
	charset="utf-8"
Content-Transfer-Encoding: quoted-printable

=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE></TITLE>
<META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
<META content=3D"MSHTML 6.00.2900.2802" name=3DGENERATOR></HEAD>
<BODY>
<P><FONT size=3D2><FONT face=3DArial>You can try this =
too:<BR><BR>...<BR>WHERE=20
EXTRACT(YEAR FROM dxdate::Date) &gt; 2000</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial><BR><BR>Mauricio Fern=C3=A1ndez =
A.<BR>Ingeniero de=20
Sistemas<BR>Universidad Aut=C3=B3noma de Manizales =
(Colombia)</FONT></FONT></P>
<P><FONT size=3D2><FONT face=3DArial></FONT><BR><BR>-----Mensaje=20
original-----<BR>De: pgsql-sql-owner@postgresql.org<BR>[<A=20
href=3D"mailto:pgsql-sql-owner@postgresql.org">mailto:pgsql-sql-owner@pos=
tgresql.org</A>]En=20
nombre de Bungsuputra Linan<BR>Enviado el: lunes, 27 febrero, 2006 =
3:42<BR>Para:=20
ken@scottshill.com; Postgres Help<BR>Asunto: Re: [SQL] Slow update=20
SQL<BR><BR><BR>Hi Ken,<BR><BR>I used to have the same problem. In my =
computer,=20
using date_part in WHERE<BR>clause will always slow down the system when =
the=20
table has plenty of rows.<BR><BR>My suggestion is try to change the =
query=20
to:<BR>... WHERE dxdate &gt;=3D '2001-01-01';<BR><BR>I hope this=20
helps.<BR><BR>Regards,<BR>bungsu<BR><BR>----- Original Message =
-----<BR>From:=20
Ken Hill<BR>To: Postgres Help<BR>Sent: Tuesday, February 14, 2006 8:48=20
AM<BR>Subject: [SQL] Slow update SQL<BR><BR><BR>I'm experiencing a very =
slow=20
query. The table contains 611,564 rows of data.<BR>I vaccumed the=20
table:<BR><BR>VACUUM ANALYZE ncccr10;<BR><BR>SELECT count(*) FROM=20
ncccr10;<BR>count<BR>--------<BR>611564<BR>(1 row)<BR><BR>When I try to =
analyze=20
the query plan with:<BR><BR>EXPLAIN ANALYZE<BR>UPDATE ncccr10<BR>SET key =
=3D=20
facilityno||'-'||<BR>lastname||'-'||<BR>sex||'-'||<BR>ssno||'-'||<BR>birt=
hdate||'-'||<BR>primarysit||'-'||<BR>dxdate||'-'||<BR>morphology3<BR>WHER=
E=20
date_part('year',dxdate) &gt; '2000';<BR><BR>The query just never =
finishes (even=20
1 hour later). The colum key100 is<BR>indexed, and I'm setting the value =
of=20
this<BR>column from other columns. Why is this so=20
slow?<BR><BR><BR>---------------------------(end of=20
broadcast)---------------------------<BR>TIP 6: explain analyze is your=20
friend<BR></P></FONT></BODY></HTML>

------=_NextPart_000_002A_01C63D21.B1F4BFA0--