X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 36BEC9FB251
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Jul 2006 07:07:41 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 26276-09 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Fri, 28 Jul 2006 07:07:19 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-
Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.192])
	by postgresql.org (Postfix) with ESMTP id 761CD9FB1E9
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Jul 2006 07:07:18 -0300 (ADT)
Received: by wx-out-0102.google.com with SMTP id t4so227309wxc
	for <pgsql-performance@postgresql.org>;
	Fri, 28 Jul 2006 03:04:01 -0700 (PDT)
DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=Z533239BSwUjEtcW4wn8Xcv7mJrsbQe7oXWaTsMJjz2xWJGx2TanEupQgNc74t1EHVBb4MlNrMZ0DIfB9BjxXVS64nqsiUmB82EmxrVnX6BZKD37z83ak4MXyGZBwy5fZidMC5hsltRduq28U4uteEeyYRmeSqHkp/1+IDeC1kg=
Received: by 10.70.125.2 with SMTP id x2mr1913456wxc;
	Fri, 28 Jul 2006 03:04:01 -0700 (PDT)
Received: by 10.70.33.19 with HTTP; Fri, 28 Jul 2006 03:04:01 -0700 (PDT)
Message-ID: <c2162c750607280304m6d155f86t2dad5bd784fa8542@mail.gmail.com>
Date: Fri, 28 Jul 2006 12:04:01 +0200
From: Eliott <eliott100@gmail.com>
To: "Scott Marlowe" <smarlowe@g2switchworks.com>, mmoncure@gmail.com
Subject: Re: performance issue with a specific query
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1154011586.31664.85.camel@state.g2switchworks.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1895_2708471.1154081041470"
References: <c2162c750607270723s628f60baqa729efbfcbeadde2@mail.gmail.com>
	<1154011586.31664.85.camel@state.g2switchworks.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.623 tagged_above=0 required=5 tests=AWL, HTML_30_40,
	HTML_MESSAGE, NORMAL_HTTP_TO_IP, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200607/263
X-Sequence-Number: 20123

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

Hi!

thanks for the quick help
i managed to reduce the response time from seemingly taking infinity to a
tolerable level, which is by the way a huge improvement.
What helped was the functional index on date_trunc('day',yourfieldhere) as
Scott suggested.
I tried to disable the geqo, but it didn't make any noticeable difference.
For now, I am happy with the result, if the result set stays the same I can
live without an upgrade.


> 1:  Which 8.xx?  8.0.x or 8.1.x?  8.1.x is literally light years ahead
> of 7.4 in terms of performance.  8.0 is somewhere between them.  The
> performance difference you're seeing is pretty common.


The benchmark was done on a small notebook running 8.1.4.1, versus 7.4.13 on
a 2gig P4 server. The difference is astounding, even without the functional
index 8.1 is 5-10 times faster than a full fledged server.

3:  You are NOT Stuck on 7.4.13.  I have a RHEL server that will be
> running 8.1.4 or so pretty soon as a dataware house.  It may get updated
> to RHEL4, may not.  You can either compile from the .tar.[gz|bz2] files
> or download the PGDG rpms for your distro.


I know, but that's was I was trying to avoid. It is not that I would use the
RHEL support provided for 7.4.13, but you know, staying official is the
whole point of subscribing to RHEL4.

Moreover, since many of our other applications are running happily running
under 7.4, I would be afraid to upgrade the whole thing.

So, again, thanks everybody for the help, you saved the day for me.

Regards
Eliott

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

Hi!<br><br>thanks for the quick help<br>i managed to reduce the response time from seemingly taking infinity to a tolerable level, which is by the way a huge improvement. <br>What helped was the functional index on date_trunc('day',yourfieldhere) as Scott suggested.
<br>I tried to disable the geqo, but it didn't make any noticeable difference.&nbsp; For now, I am happy with the result, if the result set stays the same I can live without an upgrade.<br><br><div><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<br>1:&nbsp;&nbsp;Which 8.xx?&nbsp;&nbsp;8.0.x or 
8.1.x?&nbsp;&nbsp;8.1.x is literally light years ahead<br>of 7.4 in terms of performance.&nbsp;&nbsp;8.0 is somewhere between them.&nbsp;&nbsp;The<br>performance difference you're seeing is pretty common.</blockquote><div><br>The benchmark was done on a small notebook running 
<a href="http://8.1.4.1" title="http://8.1.4.1" target="_blank" onclick="return top.js.OpenExtLink(window,event,this)">8.1.4.1</a>, versus 7.4.13 on a 2gig P4 server. The difference is astounding, even without the functional index 
8.1 is 5-10 times faster than a full fledged server. <br></div><br><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">3:&nbsp;&nbsp;You are NOT Stuck on 
7.4.13.&nbsp;&nbsp;I have a RHEL server that will be<br>running 8.1.4 or so pretty soon as a dataware house.&nbsp;&nbsp;It may get updated<br>to RHEL4, may not.&nbsp;&nbsp;You can either compile from the .tar.[gz|bz2] files
<br>or download the PGDG rpms for your distro.</blockquote><div><br>I know, but that's was I was trying to avoid. It is not that I would use the RHEL support provided for 7.4.13, but you know, staying official is the whole point of subscribing to RHEL4. 
<br></div><br><div>Moreover, since many of our other applications are  running happily running under 
7.4, I would be afraid to upgrade the whole thing. <br><br>So, again, thanks everybody for the help, you saved the day for me.<br><br>Regards<br>Eliott<br></div><br></div><br>



------=_Part_1895_2708471.1154081041470--