Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 89A1B643037 for ; Tue, 9 Feb 2010 18:22:51 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 83228-03 for ; Tue, 9 Feb 2010 22:22:35 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-fx0-f224.google.com (mail-fx0-f224.google.com [209.85.220.224]) by mail.postgresql.org (Postfix) with ESMTP id 83328642FB5 for ; Tue, 9 Feb 2010 18:22:40 -0400 (AST) Received: by fxm24 with SMTP id 24so8721fxm.3 for ; Tue, 09 Feb 2010 14:22:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :from:date:message-id:subject:to:cc:content-type :content-transfer-encoding; bh=WORRQjwtPAF5gdq3CNm++RNAZieOMNOVHA+n4VXjRlI=; b=SnlEwtYbA9++sJifCRbwSfXvyRpRsJQni/wBEPDP5A/jA0exi+HL1jSkp/CroUPT6+ jxouuusbKqrJTYfZjvFMQ9KLwbrfy1cz4cjFcfipXadE2YY7f6jta3L6lAu/JqB/pfrq YJgL1v1W+5ef6WikTLmPb9JHtdFoSYd1VZxGo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc:content-type:content-transfer-encoding; b=Rd394IDBaLB4/xVYmtkoec3sHd9qpYrGFBV9ZTPZN/7/dtvwL9xLnTmxyBjcq/JgfI gZnTM0YyKkpvTGsF5FETeaJlmfjcDBLLXlMV7Kgz1t4bvMcFlhXvDUTG7KbhrDQu6Wus j+/bD4lO/mzY+gxtnR0Gu+h58E9S5h+q3LP/8= MIME-Version: 1.0 Received: by 10.102.149.4 with SMTP id w4mr1611355mud.34.1265754157135; Tue, 09 Feb 2010 14:22:37 -0800 (PST) In-Reply-To: <1265751976.2513.34.camel@localhost> References: <1265751976.2513.34.camel@localhost> From: Thom Brown Date: Tue, 9 Feb 2010 22:22:17 +0000 Message-ID: Subject: Re: DISTINCT vs. GROUP BY To: Dimi Paun Cc: pgsql-performance Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.358 tagged_above=-10 required=5 tests=AWL=0.241, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/143 X-Sequence-Number: 37564 On 9 February 2010 21:46, Dimi Paun wrote: > >From what I've read on the net, these should be very similar, > and should generate equivalent plans, in such cases: > > SELECT DISTINCT x FROM mytable > SELECT x FROM mytable GROUP BY x > > However, in my case (postgresql-server-8.1.18-2.el5_4.1), > they generated different results with quite different > execution times (73ms vs 40ms for DISTINCT and GROUP BY > respectively): > > tts_server_db=3D# EXPLAIN ANALYZE select userdata from tagrecord where cl= ientRmaInId =3D 'CPC-RMA-00110' group by userdata; > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN > -------------------------------------------------------------------------= ------------------------------------------------------------------- > =A0HashAggregate =A0(cost=3D775.68..775.69 rows=3D1 width=3D146) (actual = time=3D40.058..40.058 rows=3D0 loops=3D1) > =A0 -> =A0Bitmap Heap Scan on tagrecord =A0(cost=3D4.00..774.96 rows=3D28= 6 width=3D146) (actual time=3D40.055..40.055 rows=3D0 loops=3D1) > =A0 =A0 =A0 =A0 Recheck Cond: ((clientrmainid)::text =3D 'CPC-RMA-00110':= :text) > =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on idx_tagdata_clientrmainid =A0(= cost=3D0.00..4.00 rows=3D286 width=3D0) (actual time=3D40.050..40.050 rows= =3D0 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((clientrmainid)::text =3D 'CPC-R= MA-00110'::text) > =A0Total runtime: 40.121 ms > > tts_server_db=3D# EXPLAIN ANALYZE select distinct userdata from tagrecord= where clientRmaInId =3D 'CPC-RMA-00109'; > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN > -------------------------------------------------------------------------= ------------------------------------------------------------------------- > =A0Unique =A0(cost=3D786.63..788.06 rows=3D1 width=3D146) (actual time=3D= 73.018..73.018 rows=3D0 loops=3D1) > =A0 -> =A0Sort =A0(cost=3D786.63..787.34 rows=3D286 width=3D146) (actual = time=3D73.016..73.016 rows=3D0 loops=3D1) > =A0 =A0 =A0 =A0 Sort Key: userdata > =A0 =A0 =A0 =A0 -> =A0Bitmap Heap Scan on tagrecord =A0(cost=3D4.00..774.= 96 rows=3D286 width=3D146) (actual time=3D72.940..72.940 rows=3D0 loops=3D1= ) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 Recheck Cond: ((clientrmainid)::text =3D 'CPC= -RMA-00109'::text) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 -> =A0Bitmap Index Scan on idx_tagdata_client= rmainid =A0(cost=3D0.00..4.00 rows=3D286 width=3D0) (actual time=3D72.936..= 72.936 rows=3D0 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((clientrmainid)::tex= t =3D 'CPC-RMA-00109'::text) > =A0Total runtime: 73.144 ms > > What gives? > Firstly, the 2 queries aren't equal. They're matching against different clientrmainid values. Also, look at the bitmap index scan for each: Bitmap Index Scan on idx_tagdata_clientrmainid (cost=3D0.00..4.00 rows=3D286 width=3D0) (actual time=3D40.050..40.050 rows=3D0 loops=3D1) Bitmap Index Scan on idx_tagdata_clientrmainid =A0(cost=3D0.00..4.00 rows=3D286 width=3D0) (actual time=3D72.936..72.936 rows=3D0 loops=3D1) That's where the difference is. An identical scan takes longer in one than the other, either due to the index scan looking for different values in each case, or at the time you were running it, another process was using more resources. You'd have to run these several times to get an idea of average times. Have you run ANALYZE on the table beforehand to make sure your stats are up to date? Regards Thom