Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id DADE19FA248 for ; Wed, 20 Dec 2006 03:26:27 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024) with ESMTP id 10951-01 for ; Wed, 20 Dec 2006 03:26:20 -0400 (AST) X-Greylist: delayed 00:06:40.833413 by SQLgrey-1.7.4 Received: from web52303.mail.yahoo.com (web52303.mail.yahoo.com [206.190.48.146]) by postgresql.org (Postfix) with SMTP id F003D9F939F for ; Wed, 20 Dec 2006 03:26:21 -0400 (AST) Received: (qmail 17384 invoked by uid 60001); 20 Dec 2006 07:19:40 -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:Content-Transfer-Encoding; b=Akuy18AoRdModoQqy2glx3RriG06BwVs7600ujFZha4ujHKaVHNiDd2iFHu69w9AfV/gLZqgVl/2wkyRKd4JBfdv4Rsi1FWDfXx/MT4cwY881noyyRDnMrNY9esk4Hs+ugQkpGzR2c7lGWKPMet1M7v7P0wfTsImU2qtkbdn2gw= ; Message-ID: <20061220071940.17382.qmail@web52303.mail.yahoo.com> Received: from [218.186.10.10] by web52303.mail.yahoo.com via HTTP; Tue, 19 Dec 2006 23:19:40 PST Date: Tue, 19 Dec 2006 23:19:39 -0800 (PST) From: Brian Herlihy Subject: GROUP BY vs DISTINCT To: Postgresql Performance MIME-Version: 1.0 Content-Type: text/plain; charset=ascii Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.015 tagged_above=0 required=5 tests=BAYES_40, DNS_FROM_RFC_ABUSE X-Spam-Level: X-Archive-Number: 200612/484 X-Sequence-Number: 22540 I have a question about the following. The table has an index on (clicked = at time zone 'PST'). I am using postgres 8.1.3=0A=0AActually, I think I an= swered my own question already. But I want to confirm - Is the GROUP BY fa= ster because it doesn't have to sort results, whereas DISTINCT must produce= sorted results? This wasn't clear to me from the documentation. If it's = true, then I could save considerable time by using GROUP BY where I have be= en using DISTINCT in the past. Usually I simply want a count of the distin= ct values, and there is no need to sort for that.=0A=0AI'm also interested = in the count(distinct) case at the bottom. The cost estimate seems similar= to the GROUP BY, but the actual cost is much higher.=0A=0AThe table is ins= ert-only and was analyzed before running these queries. The domain column = being aggregated has around 16k distinct values, and there are 780k rows in= total (for the entire table, not the slice being selected in these queries= ).=0A=0AThanks,=0ABrian=0A=0A=0Alive:parking=3D> explain analyze SELECT dom= ain =0A FROM parked_redirects=0A WHERE (clicked at time zone 'PST') >=3D = '2006-12-17'=0A AND (clicked at time zone 'PST')=0A < '2006-12-18'::tim= estamp without time zone + '1 day'::interval=0A GROUP BY domain;=0A=0A = = QUERY PLAN = =0A-----------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------=0A HashAggrega= te (cost=3D19078.50..19085.29 rows=3D679 width=3D18) (actual time=3D709.30= 9..717.096 rows=3D14526 loops=3D1)=0A -> Index Scan using parked_redirec= ts_pst on parked_redirects (cost=3D0.01..17846.82 rows=3D492672 width=3D18= ) (actual time=3D0.073..406.510 rows=3D504972 loops=3D1)=0A Index C= ond: ((timezone('PST'::text, clicked) >=3D '2006-12-17 00:00:00'::timestamp= without time zone) AND (timezone('PST'::text, clicked) < '2006-12-19 00:00= :00'::timestamp without time zone))=0A Total runtime: 719.810 ms=0A(4 rows)= =0A=0Alive:parking=3D> explain analyze SELECT DISTINCT domain=0A FROM par= ked_redirects=0A WHERE (clicked at time zone 'PST') >=3D '2006-12-17'=0A = AND (clicked at time zone 'PST')=0A < '2006-12-18'::timestamp without ti= me zone + '1 day'::interval;=0A=0A = QUERY PLAN = =0A----------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------=0A Unique (cost=3D64433.98..66897.52 rows=3D6= 79 width=3D18) (actual time=3D15329.904..15647.849 rows=3D14526 loops=3D1)= =0A -> Sort (cost=3D64433.98..65665.75 rows=3D492709 width=3D18) (actua= l time=3D15329.901..15511.479 rows=3D504972 loops=3D1)=0A Sort Key:= "domain"=0A -> Index Scan using parked_redirects_pst on parked_re= directs (cost=3D0.01..17847.41 rows=3D492709 width=3D18) (actual time=3D0.= 068..519.696 rows=3D504972 loops=3D1)=0A Index Cond: ((timezo= ne('PST'::text, clicked) >=3D '2006-12-17 00:00:00'::timestamp without time= zone) AND (timezone('PST'::text, clicked) < '2006-12-19 00:00:00'::timesta= mp without time zone))=0A Total runtime: 15666.863 ms=0A(6 rows)=0A=0Alive:= parking=3D> explain analyze SELECT count(DISTINCT domain)=0A FROM parked_= redirects=0A WHERE (clicked at time zone 'PST') >=3D '2006-12-17'=0A AND= (clicked at time zone 'PST')=0A < '2006-12-18'::timestamp without time z= one + '1 day'::interval;=0A=0A = QUERY PLAN = =0A--------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------=0A Aggregate (cost=3D19107.20..19107.21 rows=3D1 width= =3D18) (actual time=3D11380.530..11380.531 rows=3D1 loops=3D1)=0A -> Ind= ex Scan using parked_redirects_pst on parked_redirects (cost=3D0.01..17873= .67 rows=3D493412 width=3D18) (actual time=3D0.022..347.473 rows=3D504972 l= oops=3D1)=0A Index Cond: ((timezone('PST'::text, clicked) >=3D '200= 6-12-17 00:00:00'::timestamp without time zone) AND (timezone('PST'::text, = clicked) < '2006-12-19 00:00:00'::timestamp without time zone))=0A Total ru= ntime: 11384.923 ms=0A(4 rows)=0A=0A=0A