Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 30A4C9FB348 for ; Tue, 30 Jan 2007 10:12:17 -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 50298-01 for ; Tue, 30 Jan 2007 10:11:53 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 002CE9FB273 for ; Tue, 30 Jan 2007 10:11:58 -0400 (AST) Received: from server3.office.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 2EE5473946; Tue, 30 Jan 2007 14:11:49 +0000 (GMT) Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id B9D1327415B; Tue, 30 Jan 2007 14:11:48 +0000 (GMT) Message-ID: <45BF5224.3070805@archonet.com> Date: Tue, 30 Jan 2007 14:11:48 +0000 From: Richard Huxton User-Agent: IceDove 1.5.0.9 (X11/20061220) MIME-Version: 1.0 To: Simon Riggs Cc: Igor Lobanov , pgsql-performance@postgresql.org Subject: Re: Querying distinct values from a large table References: <45BF02DE.7080605@swsoft.com> <45BF0C13.3070003@archonet.com> <45BF10D7.3040203@swsoft.com> <1170165856.3681.54.camel@silverbirch.site> In-Reply-To: <1170165856.3681.54.camel@silverbirch.site> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/423 X-Sequence-Number: 23024 Simon Riggs wrote: > 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. Well, even if the sort was instant it's only going to get him down to 20secs. -- Richard Huxton Archonet Ltd