Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 5492E2E0057 for ; Tue, 22 Apr 2008 10:04:35 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 28781-02 for ; Tue, 22 Apr 2008 10:04:24 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ironport2-out.teksavvy.com (ironport2-out.pppoe.ca [206.248.154.182]) by developer.postgresql.org (Postfix) with ESMTP id 444C52E0055 for ; Tue, 22 Apr 2008 10:04:32 -0300 (ADT) X-IronPort-Anti-Spam-Filtered: true X-IronPort-Anti-Spam-Result: AmIFADJ/DUjO+Igp/2dsb2JhbACBUqp4 X-IronPort-AV: E=Sophos;i="4.25,693,1199682000"; d="scan'208";a="18863098" Received: from smtp.pppoe.ca (HELO smtp.teksavvy.com) ([65.39.196.238]) by ironport2-out.teksavvy.com with ESMTP; 22 Apr 2008 09:04:30 -0400 Received: from [206.248.136.41] ([206.248.136.41]) by smtp.teksavvy.com (Internet Mail Server v1.0) with ESMTP id CRA35030; Tue, 22 Apr 2008 09:04:30 -0400 Message-ID: <480DE25E.4080507@mark.mielke.cc> Date: Tue, 22 Apr 2008 09:04:30 -0400 From: Mark Mielke User-Agent: Thunderbird 2.0.0.12 (X11/20080226) MIME-Version: 1.0 To: Matthew Wakeling CC: Pgsql performance Subject: Re: Group by more efficient than distinct? References: <87ve2f7bel.fsf@oxford.xeocode.com> <480D283E.1060602@mark.mielke.cc> <480DD390.7020405@mark.mielke.cc> In-Reply-To: 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: 200804/315 X-Sequence-Number: 29808 Matthew Wakeling wrote: > On Tue, 22 Apr 2008, Mark Mielke wrote: >> The poster I responded to said that the memory required for a hash >> join was relative to the number of distinct values, not the number of >> rows. They gave an example of millions of rows, but only a few >> distinct values. Above, you agree with me that it it would include >> the rows (or at least references to the rows) as well. If it stores >> rows, or references to rows, then memory *is* relative to the number >> of rows, and millions of records would require millions of rows (or >> row references). > > Yeah, I think we're talking at cross-purposes, due to hash tables > being used in two completely different places in Postgres. Firstly, > you have hash joins, where Postgres loads the references to the actual > rows, and puts those in the hash table. For that situation, you want a > small number of rows. Secondly, you have hash aggregates, where > Postgres stores an entry for each "group" in the hash table, and does > not store the actual rows. For that situation, you can have a > bazillion individual rows, but only a small number of distinct groups. That makes sense with my reality. :-) Thanks, mark -- Mark Mielke