From cristian@clickdiario.com Mon Dec  19 17:33:30 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id C26239DC810
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Dec 2005 13:33:30 -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 89341-06
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Dec 2005 13:33:29 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.clickdiario.com (mail.clickdiario.com [70.85.167.114])
	by postgresql.org (Postfix) with ESMTP id DEBD49DC801
	for <pgsql-performance@postgresql.org>;
	Mon, 19 Dec 2005 13:33:27 -0400 (AST)
Received: from localhost (localhost [127.0.0.1])
	by mail.clickdiario.com (Postfix) with ESMTP id C1AB410005
	for <pgsql-performance@postgresql.org>;
	Mon, 19 Dec 2005 11:43:15 -0600 (CST)
Received: from mail.clickdiario.com ([127.0.0.1])
	by localhost (mail.clickdiario.com [127.0.0.1]) (amavisd-new,
	port 10024)
	with ESMTP id 09624-09 for <pgsql-performance@postgresql.org>;
	Mon, 19 Dec 2005 11:43:15 -0600 (CST)
Received: by mail.clickdiario.com (Postfix, from userid 5001)
	id 8B19D10065; Mon, 19 Dec 2005 11:43:15 -0600 (CST)
Received: from cristian1 (unknown [216.230.131.226])
	by mail.clickdiario.com (Postfix) with ESMTP id 747DD10005
	for <pgsql-performance@postgresql.org>;
	Mon, 19 Dec 2005 11:43:14 -0600 (CST)
From: "Cristian Prieto" <cristian@clickdiario.com>
To: <pgsql-performance@postgresql.org>
Subject: Any way to optimize GROUP BY queries?
Date: Mon, 19 Dec 2005 11:30:25 -0600
Message-ID: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
MIME-Version: 1.0
Content-Type: multipart/related;
	boundary="----=_NextPart_000_00D8_01C6048F.9BF9ED80"
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
Thread-Index: AcYEweXMWgpfo11xRACuPBLHW4LiZg==
X-Virus-Scanned: by amavisd-new-20030616-p10 (Debian) at example.com
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.406 required=5 tests=[AWL=0.405,
 HTML_MESSAGE=0.001]
X-Spam-Score: 0.406
X-Spam-Level: 
X-Archive-Number: 200512/457
X-Sequence-Number: 16278

This is a multi-part message in MIME format.

------=_NextPart_000_00D8_01C6048F.9BF9ED80
Content-Type: multipart/alternative;
	boundary="----=_NextPart_001_00D9_01C6048F.9BF9ED80"


------=_NextPart_001_00D9_01C6048F.9BF9ED80
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

I have the following table:

 

CREATE TABLE mytmp (

            Adv integer,

            Pub integer,

            Web integer,

            Tiempo timestamp,

            Num integer,

            Country varchar(2)

);

 

CREATE INDEX idx_mytmp ON mytmp(adv, pub, web);

 

And with 16M rows this query:

 

SELECT adv, pub, web, country, date_trunc('hour', tiempo), sum(num)

FROM mytmp GROUP BY adv, pub, web, country, date_trunc('hour', tiempo)

 

I've tried to create index in different columns but it seems that the group
by clause doesn't use the index in any way.

 

Is around there any stuff to accelerate the group by kind of clauses?

 

Thanks a lot.

 

 

 


------=_NextPart_001_00D9_01C6048F.9BF9ED80
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]-->
<style>
<!--
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:612.0pt 792.0pt;
	margin:72.0pt 90.0pt 72.0pt 90.0pt;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DEN-US link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I have the following =
table:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>CREATE TABLE mytmp (<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Adv
integer,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Pub
integer,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Web
integer,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Tiempo
timestamp,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Num
integer,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; Country
varchar(2)<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>);<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>CREATE INDEX idx_mytmp ON mytmp(adv, pub, =
web);<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>And with 16M rows this =
query:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>SELECT adv, pub, web, country, =
date_trunc(&#8216;hour&#8217;,
tiempo), sum(num)<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>FROM mytmp GROUP BY adv, pub, web, country, =
date_trunc(&#8216;hour&#8217;,
tiempo)<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>I&#8217;ve tried to create index in different columns =
but it
seems that the group by clause doesn&#8217;t use the index in any =
way.<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Is around there any stuff to accelerate the group by =
kind of
clauses?<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Thanks a lot&#8230;<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><img width=3D400 height=3D135 id=3D"_x0000_i1025"
src=3D"cid:image001.gif@01C6048F.9B2F2370">&nbsp;</span><o:p></o:p></font=
></p>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

</div>

</body>

</html>

------=_NextPart_001_00D9_01C6048F.9BF9ED80--

------=_NextPart_000_00D8_01C6048F.9BF9ED80
Content-Type: image/gif;
	name="image001.gif"
Content-Transfer-Encoding: base64
Content-ID: <image001.gif@01C6048F.9B2F2370>

R0lGODlhkAGHAPcAAP6yIFVVVfOVmZmZmdbW1vHx8f/x1fjCxXd3dzMzM//17v0BCOnp6aqqqq4S
FNPS0iIiIv/04HWRj//68Ht7fP/v0OTk5Pn5+VxcXOEJFa3R0IiIiBEREaelpNzc3M0oLbq6upKS
ktkQFulHUP+fo/T09MzMzIWFhf+uLfvh48bGxv/y2bKwsPbCxERERP/25WxsbP/+/B5UUfnS1P3x
8d7o53E1Mv8QGfwJEg8yLeIwNllVVO38/P+ws6CgoMnJyeMYJL6+visrK//89mlpab28u//47OHh
4np3d/7+/v/9+eQoMkNaV4GBgTs7PP38/J2dnbW1tY2NjcDCwv/03md6ef/uz+gmJhwcHf/5705O
Tvb29v1nbOxmbevs7KKioq2trdjY2P64MO51fP/ZkAAAAJWVloaDgomJjepWXnR0dPeytmRhYAsL
Dd7e3ri4uPz7+//z3GBgXw4OEf/46aenqBYWGElJSnBwcG5ub+4MExsZFeEECikmJP/uzf6nAP9m
AP+ZAP/MAGZmZu7u7k5KSf/tzP+4iP/TgP96Iv/pwPCEiv/r3f+jZv+PREFBQf+FM/69QP/Cmf9w
Ef/gzP/Wu29sa/+ZVZGOjv/Mqv6tEN3d3f/en/+td/7IYOc3Qf/pv/69P5yZmf/eoP7DUP/Yj/7O
cP7Tf/7Nb//jr97e3f7IX//ksLGwr/WkqPSip8jHx4qKiru7u/7CT2RgX+1FTYeHh//788/P0f6s
D91YXxwXFs/Pz8r//zYzMMp7gCAfHu+FhYSBfz8pKf3u7nV+frq3uPb19dPT1VBQUU9FRFhYWD4+
QO3r6zo2Nvfy8ZqanJaWk3VubWJiY9/f4OXf39Xg3kdFQNh0eLuEhslbYHJydC0tLkpKTf9dYdaS
krq6uLe3uP/d3v3MzO7u7ayrqx4eIP9tcfASGKCgn9LJx87OzUZHR4ODhISEgZCQkGZmaP8oL4R3
dfAzN19rbv78/FGGheTk47Xk48O4uNDQ0LGurf+0uJmwrv+9vf///yH5BAAAAAAALAAAAACQAYcA
AAj/AAUJFOinoMGDCBMqXMiwocOHECNKnEixosWLGDNq3MhxYoUVKyK8MHLrn8mTKFOqXMmypcmB
Al3KnEmzps2bOHPq3Mmzp8+fQIMKVRlDya0sdF5QWVHBioEIRpQMrQlT0NSrWLNq3cq1q9evNGNM
eGGgwtMsYKuCXcu2rdu3cOPOFBuhQoUIQ7qqlcu3r9+/gAOnvBXHjwEjW/cKXsy4sePHOY2UfRED
q2LImDNr3ixXLFPKUy9zHk26tOmft5giFir6tOvXsGNPMLAiL9DWJwkgSMD7BAGVJxL4WLlb1j8M
CX709ME7waPhKYNDZ/koQYHY2LMHfuHnxW2YLJk3/28+3aR04gmMI1fOU3xz4yjPt6x+Xbv9+2+H
rDBgmyfuH72ZVIB8NBUXFHMh/MPcCTnRh9+DEHoVQ12r7YTbbgmiZME/BSRABAaPhCCcgrwRUYCI
vPmA3G8F7JYAAkSMWF0I1RGxIUoIkniCLAmc8AgRIkLnQ3Uvbogcb7/5oEVv9UXo5JM7GXGXf+Cp
tOSNKXXYW5AWjIcAisKteNx4Kf5DZHMIpOQeb7LwyBsYQf4T3HhaFHBkAgSsmUCdUPbpJ01DGGBA
ZTnhduVKWv4WpxZaWGDjPwauCMaevxGw5HDVMcicFmo2pwUY//DI6T9cOgdfjJha1+UjxhWA6p+w
xv+aUgxx8FdolSlhiKhzJsV54ovXRYpnjr3KiOc/BPCKYwIZmsRjmqQKlyyZL5ppXbIYnPSsrNzG
OmEF/VGFK0puMmheeh0+UuxwCMiCLaTpjUnApFpUeqm1lSp7ErHOVhutD13Wa9J09K3a6qvdJuzn
C+DehBuJ1PqQ7rpu8sapsL/dSR6+yOo7MLPk+hvnnJ6a5CCYFjep8MoRMhyuTA+HGiNvCLCobJzi
YfAbxia5SMSr1eWr7rLNhiryiAoS+ai19SnJJMtQRxiDy+IOFPXVWGcXwaA0xZz112A7RqsBXY8b
9tloLxaDAVTM5HXacMfN1hAVVMjS23LnrXdWUr7/nBLeewcuuE9buwQ4SpuUAd9Jm0BgEgQb4LQB
BGWUkcAALkGwySAusCRL5yxtEDnkM5E++Olgre3d3Wbv1PhOG8RrdAMtae7S5y2Jjvrugt3S8EoP
D1J5vC4Ib4LmJnBQRgCyVF7GP4NgvkkClW/w/OculMGBcZu4QAj1LkBACOg8Vj7I45sMcD70wxuH
+wAQmPCP9WVAMEjk0f8zffXPbxAABxvI3/6Wp5LylWF9/wjA8DYRKhdkDwLlSwADeYe6F6yAdVZT
ySASQIh/DIAD/8je6DYXOZO8DnqYs58HKxcq7ZnABAnwYAM20DkaJlB/ZcDc90aoPuhx0IMg/NwA
/34IPxN8rwz4S+H5BsBC63Uufyr8HgJxqMME4O+Hg3Bc8zQnPMwFAFoUHNza7HYS3JShgyhxQQk1
JwsIYE5/jkMhj07CxFDFzyTPG0ADEJCmANzvfBsA40k018MzpqR5Pwwh7f6xQznG0CR1tKEjT0II
EJ4kkCoxpEkSYII2MrABj+xhGE8nJUKhxIxoPIkL4GO77p3vhNGbIySvB7p/PG96xytD9taHyZQQ
8nya1Jbl0OiCRTYylo9c4fxKiExKWtIkvURJMDmJuwZCcoqj3JvqVIKbDXbwgyFk5SaY10Y4mgSK
S2RhNW0JTdkNUX6JqyIPz+dNIDawiB6M3xGT+P8PFTKxf8xMYZqkiLgcMtKK0PMe9LRIPtCJMpuC
m0AFTPmS1plEgZYzzirRJwvlSS+OAqQeEml5kudBUqSc1BblDog+UWI0XrjrKOboZz9+DtB6yzwn
5mBYuQA4qwwMbKP5TEKIlwa1odeE6OkMsLoyWlSpUA2jRP/21KhaFXWHOWVVr8rVvZFFqxnsqlgH
pwQr9OdwY01r1uIQAaeGVa1whdtUK/rWuNoVbDGoAFr+gda7+jVWcVgdCgY72L8aFmtZINs/AsFY
xh72sSyLgR+k0ljHQvay3cpqZQOB2c7Gigpt3axnR9snySy2sqRNrdQMcdrGqva1+PHDLUQL29r/
YqdutLWtbk+zghfkdrfA5UwEIvDb4BoXMnRYQXGPy9zFGEG5qG2udBczASssd7rYjcsQDHHd7HqX
LUrgbnS/S962xEC8ri2veteCXsuu971caS9n4UvfrMi3vvidyn3zy9+f7FclLzRJgP8x4ALLj8AH
NrCAE8zgBTsYwQ9WMIQnLOEKN5jCF7ZwhDPMYQH39y///bCIbxLiEZtYJiU+sYpVkuIVu/gfLX5x
4ASwhtjEWMZ5ewYQbpABbxzANTfGcdzgsIQboOMGONDDCFSigjuEAyhbmEY7eoKMKLCkxdmrXBw1
+EaaEEJ8BHZe5HJ5x/lVTqHR1JbyUkrmA0PT/6eULDNKlKdLM4t5hbXM5fZkDAcd3CAYrrjCAkSw
kjks4yTU6Mky7NCTaKjhyt3t5wRXkr+aDADOsUMJ6SZnx000EoYpqaRxOPePy3kwmZvgwKQhhc0w
p1LNaBxALU3dgGeu+Ak6WEAK/sEDAfBhJQnIg4A4IAWetAMLPZlCHSA9XpXYjsAihXOloblllQSg
hAhAYPJOwkkElFCUHHh1A+B8QmjOuoQmaQCYU6LulaiRjrV89xBl/IQlLOAX+OjFOH6tEm7A4CQU
QAZPbMForNz42aaGHj27PBPZoZQQ65x2At8o55QEsKRlWCT9FPpl7TEcUlp2M/xSKWvGOW9xLv/O
9Tc08A9/ELrf//7HE7ZgkiPY3CQWQEYJTPKDINwDJUeYwj/esWULBGEKSUAJLqawjpPcI+kEYA+z
07sS2zXPedpDoU1UnRIXtKuW0x4EAjeqkiG++oOEqCQDvQ6pNDXv1ZdMphtRUnJGzt2TMra3Nnjw
DxK8PCX+NkkY5vBkW3DAyv/4QjIY8I88UCAdx/oHA+7whSAwwxkCXkY6YpEtk9QhAOUgAgVMkgcI
qGAAcxD41N3r7KAm8yQSlwlQ2R2A43H7wJLcJMqd1cotr7IB68OdBE1C9lBbEn4pqfs6XfBxE6vj
AwsQgElIkIGVBN4kbTADhzjAeGQxKBZOMAn/M+5gkmQI+x8ceKQQ3PE4NPwDDhxINAP28IZ/SGEO
vPiHHLzQkoNPkHTjkyaxlznyk236g1CbFke5hz6H9GylFjkdRQjJIz/v5kehEm4nAUod5EB293F1
J2oXuGomVgPy4AAO0ALT93coIQQx9w9t0AGP82j/wALg8A9C8A69MgcmwQH89w/J4DgqMAdhYBKx
gGyt8EwBwA7/YAZzcAEz4X8CFm06FUKAVG0pkQC0c0QERGCUc0f0o2WMZGsXiHWbQGbwsXEmRD17
1jwMJDyWw0CZlny11FEe52LHUAVV4AALUGN9p4InwYIn0QZfYBLvwGgXEAtOyAHLAAt1AA+M/8YA
z6QGwDB0HOCE/6ACIAQDQnASaoBssWCFqzdfXOFtPDFuQrYY0kAPTKCHPTB9C2ASSWAB1OCEgIh9
g/gPJaCDJdAE+sMBaMAA+kAN/McCbXASTWAOcqKDJoEMOrgM4WcSTcBoUmAHSYdikTYUsqQTxXSK
gSEMO8AEEvAJCyAOJtEDIgAG/dAA+VANT/AP1+eCt4h+A7AF0PAPgyd0KPEGxWgSFIBsG6CM/4AL
xSgHyUQB0lhw1ths3Jg2UHANysAgXHAD2SAB9sAENrAP1tAMcPCHLVgG8WgLfbAOHvAPFzAHXTYA
x1AAADkNyOYDHEAOJtEKOhgLCEkE3DA/1P/4hNe4kFdjDM7gC77ADsWQAzkgAxIwBdigByvhDDLo
gq1wEuQwB6O3SYdmEniQiPDRB5voBXZQBCaxDJwSBs/kBHjwD59IE0HGk1BDAELQBxDQB+aQA8Pg
ALpwDvGwADigEknAAcwQiGV5ElJ5ElHQlf8ABcX2D+3gOAwwB5aUB8kgeVigDybhBAwCBdywkRjA
ASqgkwqpllhjAbuABRwADBTAf7WAA6iJDujABSphARRgBjRHABQwAO1oEkGAJTNIBO0wAJb4D7aA
BCDwA1JQH2ZAAdsQBCdRAhTQBBQwhP9QB01QBzTnEmnpmQmjBnsgCjt3EvNwAzogAMTAFbX/qRXV
aZ0PYgWhEApWICvlaZ46MQYjMAIzgBUp0AIoyBOK8Ad/oAjseV3/VGdAQXYY9Txa+EZ0mFLZyDhq
+EZuOEXbdlCV83H7wwEMemYo8WXyo4XopjcjkAEZ8GNXsQge2hP5uZ/92Zke1Go9kWonwQGLlFAh
CIKkhn5wJ2/hpoHf86IJZ4DJ82o2Sgga+IAnYYCs1otuljcd+qEqQQw01hMiWn34qZ/8qRKpUAp7
5RVVeqVeYQiA0KVduhIPdVFntkqcZgJlcKTFY3FwRgizt4MH5m2maE4J9KJhOEiTNj3wgQBwRi7r
VqccpTk5mm7rZkM9GjhJCqIo0QUeep86//GkJCqlVKqfnvAVoCCp7OWlX1p2zvNEHJQ8ipNAu7Gh
LEGK/9AAzoM5JpWiKHFx0COqJ2F70GQ5AjY8bpY9goQSsOqGoNNxFPo41SM4h6oSaeChfNioIxql
JpoSo2CpXrGsf0AKl4qpYDpFmpQ8xvFle+oSAfBxb5eqD2V20OSqXHikjYSF8/N6x7N743quG5gm
fNRCRfVK5ro3wZoSNDAG0scTjoqsU5oSZGAKEwAW/xqwYMGl0lp21IpG1hpmGCgTzKcSnMR1kFJC
eNczrto48FFpn/NlJSVp4ZoSGEt88FFOwxdOqcqqHOqhiDoU+1oToHAKoYAKnPAPJdqvOP+RBevp
FRFQCqdwCqWQsy1hBaAACkB7ZZgKCNOKEt7kqbLwZXuUrSxxP2EoPxsQbqTWUUHlgFqHOBCUgbbz
br1qQwjQOSHLOF1rbsR3PhYYge82Pc23MjOwCF3QBYswnypRrziRAgIwBmmwCMV6Ei37DzRgn4yK
Ep6gn4grBqwAqTiRCgCAuKuQBemJCleRBaiAuIhLClp6Eo6LuQCQCv13tEmLEhjlQLJggf0UOQaV
pijxritUP9cqUphDh87DQCmloFiHOW4IZwfKQBqatgMUoSF0Zh00odwjUre6MjQwrB7avCuBt4B7
rCvxpM2rpCjRsjTwCRkABK+gEqaQuJH/8LhiwLgoUaKIsBKcgLn6Ob5/EAkrkZ6cwAma8AcA0FYp
oZ/nmxLsq75/IAabe7n8+weTuhIG66VeEac6UUm6pb0Z8AmLsAja+7wqqxKBixJj4KFLMAaLMKwr
+w/7mr3ba7coEQGIO7Mmkb7kexLmqxIkjL9WYAVkML/tuxL6GQmI674qgb8pgQgBfMMogcI9TAYE
LLpdUUlwZxNpplqv4KFdgBLde7cTnBIVbBIp4KGfQAMnMQMibBKOCsJAsMUnQQb6SbkoMQspbBIr
vMP6eQo/XMM0rJ8AWxdvnL8mkQU9jLlAK8M9rAmbC2NE7J40ob1LQBPQy8XSe70eumsu//GkM6C9
X8wSpKCfoJASKGyzaKzDKWHDf7C5bpzD9CsTmHzJd4zJizvKfzAKLPbHgCwTHpoGhBzFiAylKcG8
M/GkQOChYIwSmjzJKFGpyVq+oXwSmqwSoTDDngytLhHMYmzK+cvDzJzKB7vKrJwBTTwThezBh3wS
SVrLTMzELbHLlHzGNBvMJjHM+mvM9/sHdMwSwVzKo9zMpqzO0GzA0iwTS9DAr2y9sbwSF5wBitwS
jpqkY8ASzlwKKbEK4pzGKBHJf2C/JtHCOJzO6zzHIxzPrGASzjzKQpwSBZyp9dwS/fzEJpHL/3DN
U/wPB9DKKEED/2zI1QfCGSDSKFEB+v+ZC1cKxJY8zvLsr/q5CmXcyRINyjt9EgzdwwBwEhl9xw59
Eh2NtB/dEilwy9T8Cq+gqBKsz9EryymRpCNAY4sABB38wfcc0yuhybmACqeA0Iib0wp9EhMgw6FQ
CqVQzECNEuTsyRP91ndcAUhtygPM0ar81CoxA1JdvVfdwdis1SvNwNUb1tJL2LisEhOwv/qpCcvM
1ndNs3osqXV9Eplt10N9EhVA2ZVts6StvmJAsIAdzbiqPKCjO1rhhrp0pKnbejBqEw5oOjmRql9D
A4swAkAABGkg09oMy1ntEq+QBvc8AouAxfucxR76yCkxAYhgw2LgCcOFCIiw1KI80Sf/EQGeIAaa
EAmorN2ojNdC7d0mMQqkAADjTQaq/Q++3MMAa7SsfRLX9j3GAdtX8T0uMEEIEEy1I4I1obU8wduC
7RVZcNr9Swbcvdr0nCuCpDsbZKZoBAEzlEW/ykhZVrIGdUlQ+2YAFD1HJD4n51JjahwjhUQMqFNx
GIEgB7tpyD+8t+EJtEDz8z/imuAzkQWHq76RwNdPGNiqpFDLJHabFDnIZz2dOneDAC3I5250ekl1
Fj11x4A9tLTKo+I59GUd5IAkvmdsJ2sddDwM5E8sFE9cqLtY5DhOJM0M8AA9AQqn/ddDft+rCmbW
g0CZNnexg0YN0DlYVzm0fVFTjrYo/9Q46GY7haSwe/bhGwXmM/Xfs5dlYpaNkQRGpjhNJrCA7tkB
hYATVsAJaf24/BvRaEnkrZsme05JSISBf55ugn7EKuE/KwHblZZFWA5Mjq7iqmQcks5IlrM+2xgy
dNQ/mu5TnM7fgAzqNsEJpj7KD06dRA6CX3TkiaRAJcTkEujkoLMbLOHfE1S1hIDrAyA6auexWd6p
W85OIuuxU8g+EzREDIR2/ZROt9Srx6O7CqXrzP7poT4TEcDgp44TTQ2mw9NBuhM78mPv86PhLM7h
OG5LH0cIsu0CVBtQ+7M+jL4+pbtRqRrptVtp3dO6WgYfN2VS8LPhRYXj/26ezi4TVv+w2e9s8KpO
E0j+sTzOGAzQfW0R8y2RBdEez+pN7XhOE4kzaS+/832xCTsg524B9AQdz5hb9KF79EwfGw9QCFD/
8wHfEjRvyqZg81if9XHxACXAAK3QAZvJEirQAR1A4AzAAoXAApNWAmh/Eg9w93mPEiUgCx3QCj6P
OD3fASgH9HE++CVK9fp51CR282YPF4XQATvABmxQCJagEiWABJhvCYWACSlRBJ5vCaJwEqpQ9yZR
AoWwAychCqyPODtQCEgQ+zCYEkiACbH/+iYR805vCduJ0YyPuX1s9BEe+X1RCFxvEpiQ/CiB+1C/
9bWv98x/EmxwBiZRBLEP9ZZg/cn/uQO+r/yF0PYnwfkdwACqgBLO3vu/D/zBL87E79HGzxefL/1e
mZyFUPoncQa6bxJbDxAP/g0cKKrQQExndnT4x6BQEYL/WBTaFJENkoj/kOzIOLADxR2WSnRE9Mfk
SZQpTyrq2JKgIUAxY7qkWdPmTZw5de7k2dPnT6BBhQ79V4jhwAdGIyalhcQpElqFRhJMKnBpIVj/
2LA4g3HiVIQHIyIRS9Bpy487lHYcpdJtSp4wZQIiCpRRon+JDrk8tNelo0qMAFWyKZhwXcSJFS9m
fHPtv6RHkRay9PSpqqtWI+4QtamQqon/zpzJSLZ02YFnO35MSLFjhLexxcSdS7cx/867NvvSBHzb
92/gwes+jnwVIkGwVAtpJojJEgs2/1Q9fJjxY3Ik0cdiXH2wRMiWkWK7HUV7bstJmf5RAqRe8CFI
Ay/F/9fIESAF/w7h/yeJPsFDJgFkkr1ya0SSfxi5DxBHGNFvr0Ygye+SmBzpLREHK0kkpkMc+ceR
RgChBMN/NOSwowAH9IsRSGK6ZKBDGtkQsBmFs/HGxoh7bCDwCGKDo8w6KoIyTAZigw2pMkqqFYI8
K3I7tMSCpRBROqpgvJQA6EkumVpqpJF/OpkEzEMuEYwRBQZ0cJIREYRkEgQv8WsgSRKhZD07DUQw
ETDre7GvLweKUAFKBCQMQ8EQDP/Rw/v2QhQQBNuMqM47R7yTz38UgIRMBjVlU4FE1MNxVFKB0lGy
gYa0RBYVyDpOOUuYjKiEQqr7BxPKWsJVlAda2WGH5DTizrqyzliuIzKwPKmCLWtrKZNJNA3wH0jc
PCQTSOTMBK9OHEmzzDQdJMgRUQnSs5L/AASkz3/4W2+SQxk5pBOC7PvQLwznJSiTF8ctdyB0CVJg
Ev08DJPe+hAsdWGGb0LiVVUe7ugBsijLqiNReoxIFCQYGAgWidFCcgdMgv1HlCqFHLYEJJ7MqKTx
NGHFJy5nQm/aRCSJVr9sD5HkEjnXA6RMRgg02N+Mzk0XxnUJcrfQePUVlNHD8kX/+B9+IyI3o4AH
GvhBGP06sGGyyzabVEUAeCsSZml21sumxexXMEDQHPDOvCCllsCMKMWTEj3z6rORP2OUsD4JCx0s
L0YSrY/BD6tuHE48FabTzr/zoldTMv3aLeGzQxd9dMVGIUXtP8TwhCWgarato0zaw1r2gSChz3aC
OuFP9/w+7HM/FRPEa2wWK3RwN/jupJDBCzPccGiqByKxzhP1w0u/mPher0VA+v3887FJF3988kd1
vXz001d/ffaJOr99+OOXf37y36f/fvzz178x+/f3/38ABrAj/RNgAQ14QPYREIELZGADG+Y6P0RQ
ghOkYAUteEEMZlCDG+RgBz34FUEQhlCEIyRhCU14QhSmUIUfNERAAAA7

------=_NextPart_000_00D8_01C6048F.9BF9ED80--




From gsstark@mit.edu Mon Dec  19 20:47:45 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 546BD9DC82E
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Dec 2005 16:47: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 44043-06-2
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Mon, 19 Dec 2005 16:47:43 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from stark.xeocode.com (stark.xeocode.com [216.58.44.227])
	by postgresql.org (Postfix) with ESMTP id 31D469DC9E9
	for <pgsql-performance@postgresql.org>;
	Mon, 19 Dec 2005 16:47:39 -0400 (AST)
Received: from localhost ([127.0.0.1] helo=stark.xeocode.com)
	by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian))
	id 1EoRux-0000KR-00; Mon, 19 Dec 2005 15:47:35 -0500
To: "Cristian Prieto" <cristian@clickdiario.com>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Any way to optimize GROUP BY queries?
References: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
In-Reply-To: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 19 Dec 2005 15:47:35 -0500
Message-ID: <87wti0ak60.fsf@stark.xeocode.com>
Lines: 25
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.4
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.08 required=5 tests=[AWL=0.080]
X-Spam-Score: 0.08
X-Spam-Level: 
X-Archive-Number: 200512/464
X-Sequence-Number: 16285

"Cristian Prieto" <cristian@clickdiario.com> writes:

> SELECT adv, pub, web, country, date_trunc('hour', tiempo), sum(num)
> FROM mytmp GROUP BY adv, pub, web, country, date_trunc('hour', tiempo)
> 
> I've tried to create index in different columns but it seems that the group
> by clause doesn't use the index in any way.

If you had an index on < adv,pub,web,country,date_trunc('hour',tiemp) > then
it would be capable of using the index however it would choose not to unless
you forced it to. Using the index would be slower.

> Is around there any stuff to accelerate the group by kind of clauses?

Increase your work_mem (or sort_mem in older postgres versions), you can do
this for the server as a whole or just for this one session and set it back
after this one query. You can increase it up until it starts causing swapping
at which point it would be counter productive.

If increasing work_mem doesn't allow a hash aggregate or at least an in-memory
sort to handle it then putting the pgsql_tmp directory on a separate spindle
might help if you have any available.

-- 
greg




From jnasby@pervasive.com Tue Dec  20 19:47:13 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 001AE9DCA94
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Dec 2005 15:47:13 -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 45834-09
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Dec 2005 15:47:13 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from noel.decibel.org (unknown [67.100.216.10])
	by postgresql.org (Postfix) with ESMTP id D77CE9DCA85
	for <pgsql-performance@postgresql.org>;
	Tue, 20 Dec 2005 15:47:10 -0400 (AST)
Received: by noel.decibel.org (Postfix, from userid 1001)
	id 774C63982A; Tue, 20 Dec 2005 13:47:10 -0600 (CST)
Date: Tue, 20 Dec 2005 13:47:10 -0600
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: Greg Stark <gsstark@mit.edu>
Cc: Cristian Prieto <cristian@clickdiario.com>,
	pgsql-performance@postgresql.org
Subject: Re: Any way to optimize GROUP BY queries?
Message-ID: <20051220194710.GJ28771@pervasive.com>
References: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
	<87wti0ak60.fsf@stark.xeocode.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <87wti0ak60.fsf@stark.xeocode.com>
X-Operating-System: FreeBSD 6.0-RELEASE amd64
X-Distributed: Join the Effort!  http://www.distributed.net
User-Agent: Mutt/1.5.11
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.04 required=5 tests=[AWL=0.040]
X-Spam-Score: 0.04
X-Spam-Level: 
X-Archive-Number: 200512/508
X-Sequence-Number: 16329

On Mon, Dec 19, 2005 at 03:47:35PM -0500, Greg Stark wrote:
> Increase your work_mem (or sort_mem in older postgres versions), you can do
> this for the server as a whole or just for this one session and set it back
> after this one query. You can increase it up until it starts causing swapping
> at which point it would be counter productive.

Just remember that work_memory is per-operation, so it's easy to push
the box into swapping if the workload increases. You didn't say how much
memory you have, but I'd be careful if work_memory * max_connections
gets very much larger than your total memory.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



From tgl@sss.pgh.pa.us Tue Dec  20 21:48:10 2005
X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 228799DCB38
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Dec 2005 17:48:10 -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 73822-07
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Tue, 20 Dec 2005 17:48:04 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id C67839DCB0B
	for <pgsql-performance@postgresql.org>;
	Tue, 20 Dec 2005 17:48:00 -0400 (AST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jBKLilJM006543;
	Tue, 20 Dec 2005 16:44:47 -0500 (EST)
To: "Jim C. Nasby" <jnasby@pervasive.com>
cc: Greg Stark <gsstark@mit.edu>, Cristian Prieto <cristian@clickdiario.com>,
	pgsql-performance@postgresql.org
Subject: Re: Any way to optimize GROUP BY queries? 
In-reply-to: <20051220194710.GJ28771@pervasive.com> 
References: <00d701c604c1$e6945d80$6500a8c0@gt.ClickDiario.local>
	<87wti0ak60.fsf@stark.xeocode.com>
	<20051220194710.GJ28771@pervasive.com>
Comments: In-reply-to "Jim C. Nasby" <jnasby@pervasive.com>
	message dated "Tue, 20 Dec 2005 13:47:10 -0600"
Date: Tue, 20 Dec 2005 16:44:47 -0500
Message-ID: <6542.1135115087@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.024 required=5 tests=[AWL=0.024]
X-Spam-Score: 0.024
X-Spam-Level: 
X-Archive-Number: 200512/514
X-Sequence-Number: 16335

"Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Mon, Dec 19, 2005 at 03:47:35PM -0500, Greg Stark wrote:
>> Increase your work_mem (or sort_mem in older postgres versions), you can do
>> this for the server as a whole or just for this one session and set it back
>> after this one query. You can increase it up until it starts causing swapping
>> at which point it would be counter productive.

> Just remember that work_memory is per-operation, so it's easy to push
> the box into swapping if the workload increases. You didn't say how much
> memory you have, but I'd be careful if work_memory * max_connections
> gets very much larger than your total memory.

It's considered good practice to have a relatively small default
work_mem setting (in postgresql.conf), and then let individual sessions
push up the value locally with "SET work_mem" if they are going to
execute queries that need it.  This works well as long as you only have
one or a few such "heavy" sessions at a time.

			regards, tom lane