From ken@scottshill.com Tue Feb  14 01:48:08 2006
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 7BDE99DCC4F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 21:48:08 -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 78398-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 21:48:12 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82])
	by postgresql.org (Postfix) with ESMTP id 5EE839DCC53
	for <pgsql-sql@postgresql.org>; Mon, 13 Feb 2006 21:48:06 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc12) with SMTP
	id <20060214014809m1200a9lche>; Tue, 14 Feb 2006 01:48:09 +0000
Subject: Slow update SQL
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Postgres Help <pgsql-sql@postgresql.org>
Content-Type: multipart/alternative; boundary="=-VUIOsE4b7Z+ozBTktk0L"
Organization: Scotts Hill Associates, Inc.
Date: Mon, 13 Feb 2006 17:48:45 -0800
Message-Id: <1139881725.3183.2.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.078 required=5 tests=[AWL=0.077,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.078
X-Spam-Level: 
X-Archive-Number: 200602/144
X-Sequence-Number: 24154


--=-VUIOsE4b7Z+ozBTktk0L
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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? 


--=-VUIOsE4b7Z+ozBTktk0L
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
I'm experiencing a very slow query. The table contains 611,564 rows of data. I vaccumed the table:<BR>
<BR>
VACUUM ANALYZE ncccr10;<BR>
<BR>
SELECT count(*) FROM ncccr10;<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) &gt; '2000';<BR>
<BR>
The query just never finishes (even 1 hour later). The colum key100 is indexed, and I'm setting the value of this<BR>
column from other columns. Why is this so slow? <BR>
<BR>
</BODY>
</HTML>

--=-VUIOsE4b7Z+ozBTktk0L--




From mike@fuhr.org Tue Feb  14 02:14:36 2006
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 1FB5D9DCC1B
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 22:14:36 -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 82692-06
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Mon, 13 Feb 2006 22:14:40 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id D43EC9DC86C
	for <pgsql-sql@postgresql.org>; Mon, 13 Feb 2006 22:14:33 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k1E2EYrg055989
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Mon, 13 Feb 2006 19:14:37 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k1E2EYQg075333;
	Mon, 13 Feb 2006 19:14:34 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k1E2EYL4075332;
	Mon, 13 Feb 2006 19:14:34 -0700 (MST) (envelope-from mfuhr)
Date: Mon, 13 Feb 2006 19:14:34 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Hill <ken@scottshill.com>
Cc: Postgres Help <pgsql-sql@postgresql.org>
Subject: Re: Slow update SQL
Message-ID: <20060214021434.GA75196@winnie.fuhr.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1139881725.3183.2.camel@localhost.localdomain>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/145
X-Sequence-Number: 24155

On Mon, Feb 13, 2006 at 05:48:45PM -0800, Ken Hill wrote:
> 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? 

If EXPLAIN ANALYZE is taking too long then could we at least see
the EXPLAIN output?  How many rows does the condition match?

SELECT count(*) FROM ncccr10 WHERE date_part('year',dxdate) > '2000';

Do you have an expression index on date_part('year',dxdate)?  Does
the table have any triggers or rules?  Have you queried pg_locks
to see if the update is blocked on an ungranted lock?

Do other tables have foreign key references to ncccr10?  If so then
you might need indexes on the referring columns.

What version of PostgreSQL are you running?

-- 
Michael Fuhr



From mike@fuhr.org Tue Feb  14 05:17:29 2006
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 232419DC8AD
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 01:17:29 -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 21562-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 01:17:27 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158])
	by postgresql.org (Postfix) with ESMTP id B47909DC8A2
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 01:17:25 -0400 (AST)
Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1])
	by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id k1E5HMuv056127
	(version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK);
	Mon, 13 Feb 2006 22:17:24 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: from winnie.fuhr.org (localhost [127.0.0.1])
	by winnie.fuhr.org (8.13.4/8.13.4) with ESMTP id k1E5HML0076554;
	Mon, 13 Feb 2006 22:17:22 -0700 (MST)
	(envelope-from mfuhr@winnie.fuhr.org)
Received: (from mfuhr@localhost)
	by winnie.fuhr.org (8.13.4/8.13.4/Submit) id k1E5HLO4076553;
	Mon, 13 Feb 2006 22:17:21 -0700 (MST) (envelope-from mfuhr)
Date: Mon, 13 Feb 2006 22:17:21 -0700
From: Michael Fuhr <mike@fuhr.org>
To: Ken Hill <ken@scottshill.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Slow update SQL
Message-ID: <20060214051721.GA76364@winnie.fuhr.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
	<20060214021434.GA75196@winnie.fuhr.org>
	<1139885286.3183.10.camel@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1139885286.3183.10.camel@localhost.localdomain>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120]
X-Spam-Score: 0.12
X-Spam-Level: 
X-Archive-Number: 200602/147
X-Sequence-Number: 24157

[Please copy the mailing list on replies.]

On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > How many rows does the condition match?
>
> csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> date_part('year',dxdate) > '2000';
>  count
> --------
>  199209
> (1 row)

You're updating about a third of the table; an expression index on
date_part probably wouldn't help because the planner is likely to
stick with a sequential scan for such a large update.  Even if it
did help it's likely to be a small fraction of the total time.

The table definition you sent me showed nine indexes.  You might
see a substantial performance improvement by dropping all the
indexes, doing the update, then creating the indexes again (don't
forget to vacuum and analyze the table after the update).  However,
dropping the indexes has obvious implications for other queries so
you might need to do the update at a time when that doesn't matter.

> > Have you queried pg_locks
> > to see if the update is blocked on an ungranted lock?
> 
> I don't know what that is. How do I query pg_locks?

SELECT * FROM pg_locks;

http://www.postgresql.org/docs/7.4/static/monitoring-locks.html

> > What version of PostgreSQL are you running?
> 
> 7.4.8. Thank you for your help.

Newer versions generally perform better; consider upgrading to 8.0
or 8.1 if possible.

-- 
Michael Fuhr



From ken@scottshill.com Tue Feb  14 16:35:01 2006
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 898F39DC842
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 12:35:01 -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 62932-01
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 12:34:59 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc13.comcast.net (rwcrmhc13.comcast.net [216.148.227.153])
	by postgresql.org (Postfix) with ESMTP id 8FBAF9DC81F
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 12:34:58 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc13) with SMTP
	id <20060214163441m1300fkos5e>; Tue, 14 Feb 2006 16:34:57 +0000
Subject: Re: Slow update SQL
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-sql@postgresql.org
In-Reply-To: <20060214051721.GA76364@winnie.fuhr.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
	<20060214021434.GA75196@winnie.fuhr.org>
	<1139885286.3183.10.camel@localhost.localdomain>
	<20060214051721.GA76364@winnie.fuhr.org>
Content-Type: multipart/alternative; boundary="=-iY4l5K5D8UT+SG56auYX"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 14 Feb 2006 08:35:16 -0800
Message-Id: <1139934917.3083.2.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.081 required=5 tests=[AWL=0.080,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.081
X-Spam-Level: 
X-Archive-Number: 200602/149
X-Sequence-Number: 24159


--=-iY4l5K5D8UT+SG56auYX
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote:

> [Please copy the mailing list on replies.]
> 
> On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:
> > On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:
> > > How many rows does the condition match?
> >
> > csalgorithm=# SELECT count(*) FROM ncccr10 WHERE
> > date_part('year',dxdate) > '2000';
> >  count
> > --------
> >  199209
> > (1 row)
> 
> You're updating about a third of the table; an expression index on
> date_part probably wouldn't help because the planner is likely to
> stick with a sequential scan for such a large update.  Even if it
> did help it's likely to be a small fraction of the total time.
> 
> The table definition you sent me showed nine indexes.  You might
> see a substantial performance improvement by dropping all the
> indexes, doing the update, then creating the indexes again (don't
> forget to vacuum and analyze the table after the update).  However,
> dropping the indexes has obvious implications for other queries so
> you might need to do the update at a time when that doesn't matter.
> 
> > > Have you queried pg_locks
> > > to see if the update is blocked on an ungranted lock?
> > 
> > I don't know what that is. How do I query pg_locks?
> 
> SELECT * FROM pg_locks;
> 
> http://www.postgresql.org/docs/7.4/static/monitoring-locks.html
> 
> > > What version of PostgreSQL are you running?
> > 
> > 7.4.8. Thank you for your help.
> 
> Newer versions generally perform better; consider upgrading to 8.0
> or 8.1 if possible.
> 

Removing the indexes, running the update SQL, and then adding back the
indexes worked much faster. Thank you for you help.

--=-iY4l5K5D8UT+SG56auYX
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
On Mon, 2006-02-13 at 22:17 -0700, Michael Fuhr wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">[Please copy the mailing list on replies.]</FONT>

<FONT COLOR="#000000">On Mon, Feb 13, 2006 at 06:48:06PM -0800, Ken Hill wrote:</FONT>
<FONT COLOR="#000000">&gt; On Mon, 2006-02-13 at 19:14 -0700, Michael Fuhr wrote:</FONT>
<FONT COLOR="#000000">&gt; &gt; How many rows does the condition match?</FONT>
<FONT COLOR="#000000">&gt;</FONT>
<FONT COLOR="#000000">&gt; csalgorithm=# SELECT count(*) FROM ncccr10 WHERE</FONT>
<FONT COLOR="#000000">&gt; date_part('year',dxdate) &gt; '2000';</FONT>
<FONT COLOR="#000000">&gt;  count</FONT>
<FONT COLOR="#000000">&gt; --------</FONT>
<FONT COLOR="#000000">&gt;  199209</FONT>
<FONT COLOR="#000000">&gt; (1 row)</FONT>

<FONT COLOR="#000000">You're updating about a third of the table; an expression index on</FONT>
<FONT COLOR="#000000">date_part probably wouldn't help because the planner is likely to</FONT>
<FONT COLOR="#000000">stick with a sequential scan for such a large update.  Even if it</FONT>
<FONT COLOR="#000000">did help it's likely to be a small fraction of the total time.</FONT>

<FONT COLOR="#000000">The table definition you sent me showed nine indexes.  You might</FONT>
<FONT COLOR="#000000">see a substantial performance improvement by dropping all the</FONT>
<FONT COLOR="#000000">indexes, doing the update, then creating the indexes again (don't</FONT>
<FONT COLOR="#000000">forget to vacuum and analyze the table after the update).  However,</FONT>
<FONT COLOR="#000000">dropping the indexes has obvious implications for other queries so</FONT>
<FONT COLOR="#000000">you might need to do the update at a time when that doesn't matter.</FONT>

<FONT COLOR="#000000">&gt; &gt; Have you queried pg_locks</FONT>
<FONT COLOR="#000000">&gt; &gt; to see if the update is blocked on an ungranted lock?</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; I don't know what that is. How do I query pg_locks?</FONT>

<FONT COLOR="#000000">SELECT * FROM pg_locks;</FONT>

<FONT COLOR="#000000"><A HREF="http://www.postgresql.org/docs/7.4/static/monitoring-locks.html">http://www.postgresql.org/docs/7.4/static/monitoring-locks.html</A></FONT>

<FONT COLOR="#000000">&gt; &gt; What version of PostgreSQL are you running?</FONT>
<FONT COLOR="#000000">&gt; </FONT>
<FONT COLOR="#000000">&gt; 7.4.8. Thank you for your help.</FONT>

<FONT COLOR="#000000">Newer versions generally perform better; consider upgrading to 8.0</FONT>
<FONT COLOR="#000000">or 8.1 if possible.</FONT>

</PRE>
</BLOCKQUOTE>
Removing the indexes, running the update SQL, and then adding back the indexes worked much faster. Thank you for you help.
</BODY>
</HTML>

--=-iY4l5K5D8UT+SG56auYX--




From schabi@logix-tt.com Tue Feb  14 16:39:06 2006
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 E83E09DC83F
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 12:39:06 -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 63096-02
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 14 Feb 2006 12:39:03 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.logix-tt.com (serval.logix-tt.com [213.239.221.42])
	by postgresql.org (Postfix) with ESMTP id 4CE149DC804
	for <pgsql-sql@postgresql.org>; Tue, 14 Feb 2006 12:39:03 -0400 (AST)
Received: from kingfisher.intern.logix-tt.com (Md10e.m.pppool.de
	[89.49.209.14])
	(using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits))
	(No client certificate requested)
	by mail.logix-tt.com (Postfix) with ESMTP id 84B66244080;
	Tue, 14 Feb 2006 17:39:06 +0100 (CET)
Received: from [127.0.0.1] (localhost [127.0.0.1])
	by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 3596D18189F88;
	Tue, 14 Feb 2006 17:39:04 +0100 (CET)
Message-ID: <43F207A7.1030303@logix-tt.com>
Date: Tue, 14 Feb 2006 17:39:03 +0100
From: Markus Schaber <schabi@logix-tt.com>
Organization: Logical Tracking and Tracing International AG, Switzerland
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
X-Accept-Language: en-us, en
MIME-Version: 1.0
To: ken@scottshill.com, pgsql-sql@postgresql.org
Subject: Re: Slow update SQL
References: <1139881725.3183.2.camel@localhost.localdomain>	
	<20060214021434.GA75196@winnie.fuhr.org>	
	<1139885286.3183.10.camel@localhost.localdomain>	
	<20060214051721.GA76364@winnie.fuhr.org>
	<1139934917.3083.2.camel@localhost.localdomain>
In-Reply-To: <1139934917.3083.2.camel@localhost.localdomain>
X-Enigmail-Version: 0.93.0.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.121 required=5 tests=[AWL=0.121]
X-Spam-Score: 0.121
X-Spam-Level: 
X-Archive-Number: 200602/150
X-Sequence-Number: 24160

Hi, Ken,

Ken Hill wrote:

> Removing the indexes, running the update SQL, and then adding back the
> indexes worked much faster. Thank you for you help.

It might be a good idea to run VACUUM FULL between updating and reindexing.

If you want to CLUSTER on an index, it will be best to create this index
first, then CLUSTER the table, and then recreate the other indices.

HTH,
Markus

-- 
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org



From blinan@jogjamedianet.com Mon Feb  27 02:49:14 2006
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 E2C249DCCAA
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 26 Feb 2006 22:49:14 -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 21077-07
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Sun, 26 Feb 2006 22:49:19 -0400 (AST)
X-Greylist: delayed 00:06:23.768366 by SQLgrey-
Received: from mail.jmn.net.id (mail.jmn.net.id [202.169.224.5])
	by postgresql.org (Postfix) with ESMTP id B3AFE9DCB62
	for <pgsql-sql@postgresql.org>; Sun, 26 Feb 2006 22:48:57 -0400 (AST)
Received: (qmail 20661 invoked by uid 510); 27 Feb 2006 09:42:15 +0700
Received: from 202.169.227.42 by semur.jmn.net.id (envelope-from
	<blinan@jogjamedianet.com>, uid 509) with qmail-scanner-D0N0
	(clamdscan: 0.87/1102. spamassassin: 3.0.4. perlscan: D0N0.  
	Clear:RC:1(202.169.227.42):.
	Processed in 0.079209 secs); 27 Feb 2006 02:42:15 -0000
TuKaNG-ScAn-JMN-Mail-From: blinan@jogjamedianet.com via semur.jmn.net.id
TuKaNG-ScAn-JMN: D0N0 (Clear:RC:1(202.169.227.42):. Processed in 0.079209 secs
	Process 20654)
Received: from host-202-169-227-42.jmn.net.id (HELO server) (202.169.227.42)
	by mail.jmn.net.id with SMTP; 27 Feb 2006 09:42:15 +0700
Message-ID: <040c01c63b47$66440b50$0200a8c0@server>
From: "Bungsuputra Linan" <blinan@jogjamedianet.com>
To: <ken@scottshill.com>,
	"Postgres Help" <pgsql-sql@postgresql.org>
References: <1139881725.3183.2.camel@localhost.localdomain>
Subject: Re: Slow update SQL
Date: Mon, 27 Feb 2006 09:42:02 +0700
MIME-Version: 1.0
Content-Type: text/plain;
	charset="utf-8"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2800.1106
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0 required=5 tests=[none]
X-Spam-Score: 0
X-Spam-Level: 
X-Archive-Number: 200602/333
X-Sequence-Number: 24343

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?




From ken@scottshill.com Wed Mar  1 01:26:28 2006
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 14A189DCB72
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Feb 2006 21:26:28 -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 58748-04
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 28 Feb 2006 21:26:31 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82])
	by postgresql.org (Postfix) with ESMTP id 92DBC9DCB0D
	for <pgsql-sql@postgresql.org>; Tue, 28 Feb 2006 21:26:25 -0400 (AST)
Received: from [10.0.1.5] (c-24-20-199-254.hsd1.or.comcast.net[24.20.199.254])
	by comcast.net (rwcrmhc12) with SMTP
	id <20060301012627m12001co7ee>; Wed, 1 Mar 2006 01:26:28 +0000
Subject: Re: Slow update SQL
From: Ken Hill <ken@scottshill.com>
Reply-To: ken@scottshill.com
To: Bungsuputra Linan <blinan@jogjamedianet.com>
Cc: Postgres Help <pgsql-sql@postgresql.org>
In-Reply-To: <040c01c63b47$66440b50$0200a8c0@server>
References: <1139881725.3183.2.camel@localhost.localdomain>
	<040c01c63b47$66440b50$0200a8c0@server>
Content-Type: multipart/alternative; boundary="=-plZ5tDgLwx0ryR1unYhh"
Organization: Scotts Hill Associates, Inc.
Date: Tue, 28 Feb 2006 17:26:31 -0800
Message-Id: <1141176391.27050.0.camel@localhost.localdomain>
Mime-Version: 1.0
X-Mailer: Evolution 2.4.1 
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.093 required=5 tests=[AWL=0.092,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.093
X-Spam-Level: 
X-Archive-Number: 200602/328
X-Sequence-Number: 24338


--=-plZ5tDgLwx0ryR1unYhh
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

bungsu,

That does make the query work a bit faster. Thanks!

On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan wrote:

> 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?
> 

--=-plZ5tDgLwx0ryR1unYhh
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
  <META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
  <META NAME="GENERATOR" CONTENT="GtkHTML/3.8.1">
</HEAD>
<BODY>
bungsu,<BR>
<BR>
That does make the query work a bit faster. Thanks!<BR>
<BR>
On Mon, 2006-02-27 at 09:42 +0700, Bungsuputra Linan 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 &gt;= '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) &gt; '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>
</BODY>
</HTML>

--=-plZ5tDgLwx0ryR1unYhh--




From mfacontacto@ono.com Wed Mar  1 10:22:45 2006
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--