Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 697429FB41B for ; Tue, 30 Jan 2007 13:00:44 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 13490-09 for ; Tue, 30 Jan 2007 12:59:12 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id E34819FA0E7 for ; Tue, 30 Jan 2007 12:59:11 -0400 (AST) Received: (qmail 25055 invoked by uid 500); 30 Jan 2007 16:44:35 -0000 Date: Tue, 30 Jan 2007 10:44:35 -0600 From: Bruno Wolff III To: Igor Lobanov Cc: pgsql-performance@postgresql.org Subject: Re: Querying distinct values from a large table Message-ID: <20070130164435.GA22099@wolff.to> Mail-Followup-To: Bruno Wolff III , Igor Lobanov , pgsql-performance@postgresql.org References: <45BF02DE.7080605@swsoft.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <45BF02DE.7080605@swsoft.com> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/431 X-Sequence-Number: 23032 On Tue, Jan 30, 2007 at 14:33:34 +0600, Igor Lobanov wrote: > Greetings! > > I have rather large table with about 5 millions of rows and a dozen of > columns. Let's suppose that columns are named 'a', 'b', 'c' etc. I need > to query distinct pairs of ('a';'b') from this table. > > Is there any way to somehow improve the performance of this operation? > Table can not be changed. DISTINCT currently can't use a hash aggregate plan and will use a sort. If there aren't many distinct values, the hash aggregate plan will run much faster. To get around this limitation, rewrite the query as a group by. Something like: SELECT a, b FROM table GROUP BY a, b;