Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 9C35B9FB3F0 for ; Tue, 30 Jan 2007 10:06: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 84309-06 for ; Tue, 30 Jan 2007 10:06:17 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail01.enterprisedb.com (mail01.enterprisedb.com [63.246.7.168]) by postgresql.org (Postfix) with ESMTP id D47F79FB1DA for ; Tue, 30 Jan 2007 10:06:22 -0400 (AST) thread-index: AcdEd9VU6SakguNhQFq84+BUWr+W3g== Received: from [172.24.32.20] ([62.232.55.118]) by mail01.enterprisedb.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 30 Jan 2007 09:06:26 -0500 Subject: Re: Querying distinct values from a large table From: "Simon Riggs" Content-Class: urn:content-classes:message Importance: normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.2826 To: "Igor Lobanov" Cc: "Richard Huxton" , In-Reply-To: <45BF10D7.3040203@swsoft.com> References: <45BF02DE.7080605@swsoft.com> <45BF0C13.3070003@archonet.com> <45BF10D7.3040203@swsoft.com> Content-Type: text/plain; charset="iso-8859-1" Date: Tue, 30 Jan 2007 14:04:15 +0000 Message-ID: <1170165856.3681.54.camel@silverbirch.site> MIME-Version: 1.0 X-Mailer: Evolution 2.6.0 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 30 Jan 2007 14:06:27.0062 (UTC) FILETIME=[D51D6960:01C74477] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/422 X-Sequence-Number: 23023 On Tue, 2007-01-30 at 15:33 +0600, Igor Lobanov 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. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com