Received: from localhost (maia-2.hub.org [200.46.204.187])
	by postgresql.org (Postfix) with ESMTP id 185E89FB348
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 30 Jan 2007 10:13:39 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024)
	with ESMTP id 87009-04 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 30 Jan 2007 10:13:25 -0400 (AST)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4
Received: from nf-out-0910.google.com (nf-out-0910.google.com
 [64.233.182.190])
	by postgresql.org (Postfix) with ESMTP id 4E9DE9FB273
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Jan 2007 10:13:31 -0400 (AST)
Received: by nf-out-0910.google.com with SMTP id x4so211763nfb
	for <pgsql-performance@postgresql.org>;
	Tue, 30 Jan 2007 06:13:29 -0800 (PST)
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references;
	b=a+mt4f1R/FJmEOH+8bj0Ba90HYXMF9ZX4Kpz8W4EslNBDvlkkR8OQ8doPfif/r1QRJOQwE4S3i4ks6MTBoCK/1n95qLIaJ7X0OLHeaTZZnnYXojU85n+E4H9Nn3TuqxUOeHGluqOWXKVJI9bNSUEZsMaopJQ6vX/rlmYlkM4INU=
Received: by 10.49.20.1 with SMTP id x1mr801142nfi.1170166408097;
	Tue, 30 Jan 2007 06:13:28 -0800 (PST)
Received: by 10.48.210.4 with HTTP; Tue, 30 Jan 2007 06:13:27 -0800 (PST)
Message-ID: <81961ff50701300613g25ea4ce6jb357c82fb1ed6733@mail.gmail.com>
Date: Tue, 30 Jan 2007 09:13:27 -0500
From: "Chad Wagner" <chad.wagner@gmail.com>
To: "Simon Riggs" <simon@2ndquadrant.com>
Subject: Re: Querying distinct values from a large table
Cc: "Igor Lobanov" <ilobanov@swsoft.com>, "Richard Huxton" <dev@archonet.com>,
	pgsql-performance@postgresql.org
In-Reply-To: <1170165856.3681.54.camel@silverbirch.site>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_155470_29396889.1170166407613"
References: <45BF02DE.7080605@swsoft.com> <45BF0C13.3070003@archonet.com>
	<45BF10D7.3040203@swsoft.com>
	<1170165856.3681.54.camel@silverbirch.site>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200701/424
X-Sequence-Number: 23025

------=_Part_155470_29396889.1170166407613
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/30/07, Simon Riggs <simon@2ndquadrant.com> wrote:
>
> > explain analyze select distinct a, b from tbl
> >
> > EXPLAIN ANALYZE output is:
> >
> >   Unique  (cost=500327.32..525646.88 rows=1848 width=6) (actual
> > time=52719.868..56126.356 rows=5390 loops=1)
> >     ->  Sort  (cost=500327.32..508767.17 rows=3375941 width=6) (actual
> > time=52719.865..54919.989 rows=3378864 loops=1)
> >           Sort Key: a, b
> >           ->  Seq Scan on tbl  (cost=0.00..101216.41 rows=3375941
> > width=6) (actual time=16.643..20652.610 rows=3378864 loops=1)
> >   Total runtime: 57307.394 ms
>
> All your time is in the sort, not in the SeqScan.
>
> Increase your work_mem.
>

Sounds like an opportunity to implement 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 records.

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


-- 
Chad
http://www.postgresqlforums.com/

------=_Part_155470_29396889.1170166407613
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 1/30/07, <b class="gmail_sendername">Simon Riggs</b> &lt;<a href="mailto:simon@2ndquadrant.com">simon@2ndquadrant.com</a>&gt; wrote:<div><span class="gmail_quote"></span><blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
&gt; explain analyze select distinct a, b from tbl<br>&gt;<br>&gt; EXPLAIN ANALYZE output is:<br>&gt;<br>&gt;&nbsp;&nbsp; Unique&nbsp;&nbsp;(cost=500327.32..525646.88 rows=1848 width=6) (actual<br>&gt; time=52719.868..56126.356 rows=5390 loops=1)
<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;Sort&nbsp;&nbsp;(cost=500327.32..508767.17 rows=3375941 width=6) (actual<br>&gt; time=52719.865..54919.989 rows=3378864 loops=1)<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Sort Key: a, b<br>&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; -&gt;&nbsp;&nbsp;Seq Scan on tbl&nbsp;&nbsp;(cost=0.00..101216.41
 rows=3375941<br>&gt; width=6) (actual time=16.643..20652.610 rows=3378864 loops=1)<br>&gt;&nbsp;&nbsp; Total runtime: 57307.394 ms<br><br>All your time is in the sort, not in the SeqScan.<br><br>Increase your work_mem.<br></blockquote>
</div><br>Sounds like an opportunity to implement a &quot;Sort Unique&quot; (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 records.<br><br>I am assuming this optimization just isn&#39;t implemented in PostgreSQL?
<br><br clear="all"><br>-- <br>Chad<br><a href="http://www.postgresqlforums.com/">http://www.postgresqlforums.com/</a>

------=_Part_155470_29396889.1170166407613--