Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 228889FB3F0 for ; Tue, 30 Jan 2007 10:38:23 -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 38229-08 for ; Tue, 30 Jan 2007 10:38:13 -0400 (AST) X-Greylist: from auto-whitelisted 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 2ED6A9FB402 for ; Tue, 30 Jan 2007 10:38:13 -0400 (AST) Received: (qmail 85542 invoked by uid 60001); 30 Jan 2007 14:38:11 -0000 Message-ID: <20070130143811.85540.qmail@web52303.mail.yahoo.com> DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.au; h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID; b=CtfC5sMikDer7i6jjo2L99GbfhzF0WtdSuXxDLhkBqHYdm6o9o3QRU+xD2vBMHl+N/0N7Z/Y6rihhpa6DyOj32uMQH2BHfYKfv4SHp1GEM1egncVR1A/rrRRXVj+weEMb+mxMNTWXteqVfJlPopMcs6x7hKZBkAdhpo8DsNerrk=; X-YMail-OSG: Y1.sEh8VM1mBzEn2KjqvTwvPa1cI3plJaeVzFP7U7Hxpx3ecrIJBA1mwQNd.52W5c6uaI.zg6kbxaHXI7w69O1Jj9fwi4hcRtVBxTXME_kdRmVNvjkTIy3q1xpwoEkZjZoYLtsQcUjSaLDk- Received: from [202.156.8.12] by web52303.mail.yahoo.com via HTTP; Tue, 30 Jan 2007 06:38:11 PST X-Mailer: YahooMailRC/368.3 YahooMailWebService/0.6.132.7 Date: Tue, 30 Jan 2007 06:38:11 -0800 (PST) From: Brian Herlihy Subject: Re: Querying distinct values from a large table To: Postgresql Performance MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-935268814-1170167891=:84438" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/425 X-Sequence-Number: 23026 --0-935268814-1170167891=:84438 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable As I understand, there's no hashing for DISTINCT, but there is for GROUP BY= . GROUP BY will choose between a hash and a sort (or maybe other options?)= depending on the circumstances. So you can write=0A=0ASELECT a, b FROM tb= l GROUP BY a,b=0A=0Aand the sort/unique part of the query may run faster.= =0A=0ABrian=0A=0A----- Original Message ----=0AFrom: Chad Wagner =0ATo: Simon Riggs =0ACc: Igor Lobanov = ; Richard Huxton ; pgsql-performance= @postgresql.org=0ASent: Tuesday, 30 January, 2007 10:13:27 PM=0ASubject: Re= : [PERFORM] Querying distinct values from a large table=0A=0AOn 1/30/07, Si= mon Riggs wrote:=0A> explain analyze select distinc= t a, b from tbl=0A>=0A> EXPLAIN ANALYZE output is:=0A>=0A> Unique (cost= =3D500327.32..525646.88 rows=3D1848 width=3D6) (actual=0A> time=3D52719.868= ..56126.356 rows=3D5390 loops=3D1)=0A=0A> -> Sort (cost=3D500327.32..= 508767.17 rows=3D3375941 width=3D6) (actual=0A> time=3D52719.865..54919.989= rows=3D3378864 loops=3D1)=0A> Sort Key: a, b=0A> -> S= eq Scan on tbl (cost=3D0.00..101216.41=0A rows=3D3375941=0A> width=3D6) (a= ctual time=3D16.643..20652.610 rows=3D3378864 loops=3D1)=0A> Total runtim= e: 57307.394 ms=0A=0AAll your time is in the sort, not in the SeqScan.=0A= =0AIncrease your work_mem.=0A=0A=0A=0ASounds like an opportunity to impleme= nt a "Sort Unique" (sort of like a hash, I guess), there is no need to push= 3M rows through a sort algorithm to only shave it down to 1848 unique reco= rds.=0A=0AI am assuming this optimization just isn't implemented in Postgre= SQL?=0A=0A=0A=0A-- =0AChad=0Ahttp://www.postgresqlforums.com/=0A=0A=0A=0A --0-935268814-1170167891=:84438 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable

As I understand, there's no hashing for DISTINCT, b= ut there is for GROUP BY. GROUP BY will choose between a hash and a s= ort (or maybe other options?) depending on the circumstances. So you = can write

SELECT a, b FROM tbl GROUP BY a,b

and the sort/uniq= ue part of the query may run faster.

Brian

----- Orig= inal Message ----
From: Chad Wagner <chad.wagner@gmail.com>
To:= Simon Riggs <simon@2ndquadrant.com>
Cc: Igor Lobanov <ilobanov= @swsoft.com>; Richard Huxton <dev@archonet.com>; pgsql-performance= @postgresql.org
Sent: Tuesday, 30 January, 2007 10:13:27 PM
Subject: = Re: [PERFORM] Querying distinct values from a large table

On 1/30/07, Simon Riggs <simon@2ndquadrant.com> wrote= :

=0A> explain analyze select distinct a, b from t= bl
>
> EXPLAIN ANALYZE output is:
>
>   U= nique  (cost=3D500327.32..525646.88 rows=3D1848 width=3D6) (actua= l
> time=3D52719.868..56126.356 rows=3D5390 loops=3D1)=0A
>&nbs= p;    ->  Sort  (cost=3D500327.32..50= 8767.17 rows=3D3375941 width=3D6) (actual
> time=3D52719.865..54919.9= 89 rows=3D3378864 loops=3D1)
>      &nb= sp;    Sort Key: a, b
>     &= nbsp;     ->  Seq Scan on tbl  (= cost=3D0.00..101216.41=0A rows=3D3375941
> width=3D6) (actual time=3D= 16.643..20652.610 rows=3D3378864 loops=3D1)
>   Total runti= me: 57307.394 ms

All your time is in the sort, not in the SeqScan.
Increase your work_mem.

=0A

Sounds like an o= pportunity to implement a "Sort Unique" (sort of like a hash, I guess), the= re is no need to push 3M rows through a sort algorithm to only shave it dow= n to 1848 unique records.

I am assuming this optimization just isn't= implemented in PostgreSQL?=0A

--
Chad
http://www.postgresqlforums.com/=0A

--0-935268814-1170167891=:84438--