Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 4EDD79FA2C4 for ; Tue, 9 Jan 2007 21:07:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 75884-04-2 for ; Tue, 9 Jan 2007 21:07:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from web52315.mail.yahoo.com (web52315.mail.yahoo.com [206.190.48.158]) by postgresql.org (Postfix) with SMTP id 636379FB1CC for ; Tue, 9 Jan 2007 21:07:05 -0400 (AST) Received: (qmail 7817 invoked by uid 60001); 10 Jan 2007 01:07:04 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type; b=XtuXHufCaLZgo4DByheOwD/qw1Rxnat1pzvSI8Eaweg2Zv6ML2f4cP8qv94EghfhrjOqrBor3bv2hkiu6DkqU+fP51LytcNE8L+uqNzCIEIB11NckGPoKzQ58bOgmHQLKWz5wg2QbgOLcUBp9ijM/V+YN0ktNJ28DKevmv+Zrg8= ; Message-ID: <20070110010703.7815.qmail@web52315.mail.yahoo.com> Received: from [202.156.8.12] by web52315.mail.yahoo.com via HTTP; Tue, 09 Jan 2007 17:07:03 PST Date: Tue, 9 Jan 2007 17:07:03 -0800 (PST) From: Brian Herlihy Subject: Re: group by will not use an index? To: pgsql-performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-2020881089-1168391223=:7599" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/125 X-Sequence-Number: 22726 --0-2020881089-1168391223=:7599 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable Actually, as I recently discovered, GROUP BY is faster than DISTINCT. It's= just due to how they are implemented, so don't go looking for any deep rea= son :) The thread "GROUP BY vs DISTINCT" from 2006-12-20 discusses it. DI= STINCT sorts the results to find the unique rows, but GROUP BY uses a hash.= =0A=0ABrian=0A=0A----- Original Message ----=0AFrom: Adam Rich =0ATo: tsuraan ; pgsql-performance =0ASent: Wednesday, 10 January, 2007 7:32:50 AM=0ASu= bject: Re: [PERFORM] group by will not use an index?=0A=0AMessage=0A=0A =0A= =0A=0A=0AThat =0Aquery looks strange to me (a group by without an aggregate= ). See if this =0Ais=0A=0Aany =0Afaster:=0A=0A =0A=0ASELECT =0ADISTINCT DA= TE(inserted) FROM Messages=0A=0A =0A=0AI =0Awon't hold my breath though, I = don't think there's any way around the full table =0Ascan=0A=0Ain =0APostgr= es, because the index does not contain enough information about =0Atransact= ional=0A=0Astate, =0Aso table access is always required (unlike virtually e= very other type of =0Adb)=0A=0A =0A=0A=0A =0A=0A -----Original Message---= --=0AFrom: =0A pgsql-performance-owner@postgresql.org =0A [mailto:pgsql-p= erformance-owner@postgresql.org] On Behalf Of =0A tsuraan=0ASent: Tuesday,= January 09, 2007 5:06 PM=0ATo: =0A pgsql-performance=0ASubject: [PERFORM]= group by will not use an =0A index?=0A=0A=0AI have a table of messages wi= th paths and inserted =0A dates (among other things), like so:=0A=0ACREATE= TABLE Messages =0A (=0A msgkey BIGSERIAL PRIMARY KEY,=0A =0A path = TEXT NOT NULL,=0A inserted TIMESTAMP WITHOUT TIMEZONE =0A DEFAULT NOW()= =0A);=0A=0AI run a query to determine which days actually saw =0A emails = come in, like so:=0A=0ASELECT DATE(inserted) FROM Messages GROUP BY =0A DA= TE(inserted);=0A=0AThat's obviously not very efficient, so I made an =0A i= ndex: =0A=0ACREATE INDEX messages_date_inserted_ind ON =0A Messages(DATE(i= nserted));=0A=0AHowever, GROUP BY does not use this =0A index:=0A=0A=3D# e= xplain analyze select date(inserted) from messages group by =0A date(inser= ted);=0A =0A QUER= Y =0A PLAN =0A = =0A------------------------------------------------------------------------= ------------------------------------------------------=0A HashAggregate = =0A (cost=3D104773.10..104789.51 rows=3D1313 width=3D8) (actual =0A time= =3D31269.476..31269.557 rows=3D44 loops=3D1)=0A -> Seq =0A Scan on mess= ages (cost=3D0.00..101107.25 rows=3D1466340 width=3D8) (actual =0A time= =3D23.923..25248.400 rows=3D1467036 loops=3D1)=0A Total runtime: 31269.735 = =0A ms=0A(3 rows)=0A=0A=0AIs it possible to get pg to use an index in a gr= oup =0A by? I don't see why it wouldn't be possible, but maybe I'm missin= g =0A something. =0A=0AUsing pg 8.1.4...=0A=0A=0A=0A --0-2020881089-1168391223=:7599 Content-Type: text/html; charset=ascii Content-Transfer-Encoding: quoted-printable
Actually, as I recently discovered, GROUP BY is fas= ter than DISTINCT.  It's just due to how they are implemented, so don'= t go looking for any deep reason :)  The thread "GROUP BY vs DISTINCT"= from 2006-12-20 discusses it.  DISTINCT sorts the results to find the= unique rows, but GROUP BY uses a hash.

Brian

----- O= riginal Message ----
From: Adam Rich <adam.r@sbcglobal.net>
To:= tsuraan <tsuraan@gmail.com>; pgsql-performance <pgsql-performance= @postgresql.org>
Sent: Wednesday, 10 January, 2007 7:32:50 AM
Subj= ect: Re: [PERFORM] group by will not use an index?

Message</t= itle>=0A=0A =0A=0A=0A=0A<div><font color=3D"#0000ff" face=3D"Arial" size=3D= "2"><span class=3D"562163023-09012007">That =0Aquery looks strange to me (a= group by without an aggregate).  See if this =0Ais</span></font></div= >=0A<div><font color=3D"#0000ff" face=3D"Arial" size=3D"2"><span class=3D"5= 62163023-09012007">any =0Afaster:</span></font></div>=0A<div><font color=3D= "#0000ff" face=3D"Arial" size=3D"2"><span class=3D"562163023-09012007"></sp= an></font> </div>=0A<div><font color=3D"#0000ff" face=3D"Arial" size= =3D"2"><span class=3D"562163023-09012007">SELECT =0ADISTINCT DATE(inserted)= FROM Messages</span></font></div>=0A<div><font color=3D"#0000ff" face=3D"A= rial" size=3D"2"><span class=3D"562163023-09012007"></span></font> </d= iv>=0A<div><font color=3D"#0000ff" face=3D"Arial" size=3D"2"><span class=3D= "562163023-09012007">I =0Awon't hold my breath though, I don't think there'= s any way around the full table =0Ascan</span></font></div>=0A<div><font co= lor=3D"#0000ff" face=3D"Arial" size=3D"2"><span class=3D"562163023-09012007= ">in =0APostgres, because the index does not contain enough information abo= ut =0Atransactional</span></font></div>=0A<div><font color=3D"#0000ff" face= =3D"Arial" size=3D"2"><span class=3D"562163023-09012007">state, =0Aso table= access is always required (unlike virtually every other type of =0Adb)</sp= an></font></div>=0A<div><font color=3D"#0000ff" face=3D"Arial" size=3D"2"><= /font> </div>=0A<blockquote style=3D"margin-right: 0px;">=0A <div></d= iv>=0A <div class=3D"OutlookMessageHeader" dir=3D"ltr" align=3D"left" lang= =3D"en-us"><font face=3D"Tahoma" size=3D"2">-----Original Message-----<br><= b>From:</b> =0A pgsql-performance-owner@postgresql.org =0A [mailto:pgsql-= performance-owner@postgresql.org] <b>On Behalf Of =0A </b>tsuraan<br><b>Se= nt:</b> Tuesday, January 09, 2007 5:06 PM<br><b>To:</b> =0A pgsql-performa= nce<br><b>Subject:</b> [PERFORM] group by will not use an =0A index?<br><b= r></font></div>I have a table of messages with paths and inserted =0A date= s (among other things), like so:<br><br>CREATE TABLE Messages =0A (<br>&nb= sp;   msgkey BIGSERIAL PRIMARY KEY,<br>    =0A pa= th TEXT NOT NULL,<br>    inserted TIMESTAMP WITHOUT TIMEZONE= =0A DEFAULT NOW() <br>);<br><br>I run a query to determine which days act= ually saw =0A emails come in, like so:<br><br>SELECT DATE(inserted) FROM M= essages GROUP BY =0A DATE(inserted);<br><br>That's obviously not very effi= cient, so I made an =0A index: <br><br>CREATE INDEX messages_date_inserted= _ind ON =0A Messages(DATE(inserted));<br><br>However, GROUP BY does not us= e this =0A index:<br><br>=3D# explain analyze select date(inserted) from m= essages group by =0A date(inserted);<br>     &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;  =0A QUERY =0A PLAN       &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;            =0A= <br>---------------------------------------------------------------------= ---------------------------------------------------------<br> HashAggr= egate  =0A (cost=3D104773.10..104789.51 rows=3D1313 width=3D8) (actua= l =0A time=3D31269.476..31269.557 rows=3D44 loops=3D1)<br>   -&g= t;  Seq =0A Scan on messages  (cost=3D0.00..101107.25 rows=3D146= 6340 width=3D8) (actual =0A time=3D23.923..25248.400 rows=3D1467036 loops= =3D1)<br> Total runtime: 31269.735 =0A ms<br>(3 rows)<br><br><br>Is i= t possible to get pg to use an index in a group =0A by?  I don't see = why it wouldn't be possible, but maybe I'm missing =0A something. <br><br>= Using pg 8.1.4...<br></blockquote></div><br></div></div></body></html> --0-2020881089-1168391223=:7599--