Received: from localhost (pluto.hub.org [200.46.204.4])
	by postgresql.org (Postfix) with ESMTP id D84C69F9EF0
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 16 Nov 2006 04:54:01 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.4]) (amavisd-new, port 10024)
	with ESMTP id 63241-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Thu, 16 Nov 2006 04:53:50 -0400 (AST)
X-Greylist: delayed 00:06:30.375304 by SQLgrey-
Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.239])
	by postgresql.org (Postfix) with ESMTP id A44D49F9ED7
	for <pgsql-performance@postgresql.org>;
	Thu, 16 Nov 2006 04:53:58 -0400 (AST)
Received: by qb-out-0506.google.com with SMTP id e21so29733qba
	for <pgsql-performance@postgresql.org>;
	Thu, 16 Nov 2006 00:53:57 -0800 (PST)
Received: by 10.64.156.3 with SMTP id d3mr496738qbe.1163666843099;
	Thu, 16 Nov 2006 00:47:23 -0800 (PST)
Received: by 10.64.27.8 with HTTP; Thu, 16 Nov 2006 00:47:23 -0800 (PST)
Message-ID: <e38bba340611160047u27643e09wc982da68f4bedfe@mail.gmail.com>
Date: Thu, 16 Nov 2006 16:47:23 +0800
From: "AMIR FRANCO D. JOVEN" <amir@digi.ph>
To: "Markus Schaber" <schabi@logix-tt.com>
Subject: Re: Slow SELECT on three or more clients
Cc: pgsql-performance@postgresql.org
In-Reply-To: <455B2868.7010303@logix-tt.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_17959_8162191.1163666843046"
References: <e38bba340611150337r1c9a895nd99d95ca8408b88c@mail.gmail.com>
	<455B2868.7010303@logix-tt.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200611/118
X-Sequence-Number: 21888

------=_Part_17959_8162191.1163666843046
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Markus,

Thank you very much for the information.

I was able to make it fast by correcting indices, i created index on
frequently filtered fields.
now it runs at 0.05 seconds average, much faster than before 0.86.

I will also upgrade to 8.1.5.

Once again, thank you very much. it helped me a lot.

Amir

On 11/15/06, Markus Schaber <schabi@logix-tt.com> wrote:
>
> Hi, Amir,
>
> AMIR FRANCO D. JOVEN wrote:
>
> > My current project uses PostgreSQL 7.3.4.
>
> By all means, please upgrade.
>
> The newest 7.3 series version is 7.3.16, which fixes lots of critical
> bugs, and can be used as a drop-in replacement for 7.3.4 (see Release
> Notes at http://www.postgresql.org/docs/7.3/interactive/release.html )
>
> The newest stable release is 8.1.5, and 8.2 is just on the roads...
>
> > I have a table with 94 fields and a select with only one resultset in
> > only one client consumes about 0.86 seconds.
>
> "with only on resultset"?
>
> You mean "with only one returned row", I presume.
>
> Each SELECT has exactly one resultset, which can contain zero to many
> rows.
>
> Please check the following:
>
> - Did you create the appropriate indices?
>
> - Version 7.3.X may suffer from index bloat, so REINDEX might help.
>
> - Did you VACUUM and ANALYZE the table properly?
>
> - Is your free space map setting, the statistics targets, and other
> config options tuned to fit your environment?
>
> - Maybe a VACUUM FULL or a CLUSTER command may help you.
>
> > for a single select with one field in one resultset, is 0.86 seconds
> normal?
>
> That depends on the circumstances.
>
> Markus
>
> --
> Markus Schaber | Logical Tracking&Tracing International AG
> Dipl. Inf.     | Software Development GIS
>
> Fight against software patents in Europe! www.ffii.org
> www.nosoftwarepatents.org
>



-- 
AMIR FRANCO D. JOVEN
Software Engineer
DIGI Software (PHILS.) Inc.

------=_Part_17959_8162191.1163666843046
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi Markus,<br>
<br>
Thank you very much for the information.<br>
<br>
I was able to make it fast by correcting indices, i created index on frequently filtered fields.<br>
now it runs at 0.05 seconds average, much faster than before 0.86.<br>
<br>
I will also upgrade to 8.1.5.<br>
<br>
Once again, thank you very much. it helped me a lot.<br>
<br>
Amir<br><br><div><span class="gmail_quote">On 11/15/06, <b class="gmail_sendername">Markus Schaber</b> &lt;<a href="mailto:schabi@logix-tt.com">schabi@logix-tt.com</a>&gt; wrote:</span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Hi, Amir,<br><br>AMIR FRANCO D. JOVEN wrote:<br><br>&gt; My current project uses PostgreSQL 7.3.4.<br><br>By all means, please upgrade.<br><br>The newest 7.3 series version is 7.3.16, which fixes lots of critical<br>bugs, and can be used as a drop-in replacement for 
7.3.4 (see Release<br>Notes at <a href="http://www.postgresql.org/docs/7.3/interactive/release.html">http://www.postgresql.org/docs/7.3/interactive/release.html</a> )<br><br>The newest stable release is 8.1.5, and 8.2 is just on the roads...
<br><br>&gt; I have a table with 94 fields and a select with only one resultset in<br>&gt; only one client consumes about 0.86 seconds.<br><br>&quot;with only on resultset&quot;?<br><br>You mean &quot;with only one returned row&quot;, I presume.
<br><br>Each SELECT has exactly one resultset, which can contain zero to many rows.<br><br>Please check the following:<br><br>- Did you create the appropriate indices?<br><br>- Version 7.3.X may suffer from index bloat, so REINDEX might help.
<br><br>- Did you VACUUM and ANALYZE the table properly?<br><br>- Is your free space map setting, the statistics targets, and other<br>config options tuned to fit your environment?<br><br>- Maybe a VACUUM FULL or a CLUSTER command may help you.
<br><br>&gt; for a single select with one field in one resultset, is 0.86 seconds normal?<br><br>That depends on the circumstances.<br><br>Markus<br><br>--<br>Markus Schaber | Logical Tracking&amp;Tracing International AG
<br>Dipl. Inf.&nbsp;&nbsp;&nbsp;&nbsp; | Software Development GIS<br><br>Fight against software patents in Europe! <a href="http://www.ffii.org">www.ffii.org</a><br><a href="http://www.nosoftwarepatents.org">www.nosoftwarepatents.org</a><br>
</blockquote></div><br><br clear="all"><br>-- <br>AMIR FRANCO D. JOVEN<br>Software Engineer<br>DIGI Software (PHILS.) Inc.

------=_Part_17959_8162191.1163666843046--