From: Mark Mielke <mark@mark.mielke.cc>
To: Matthew Wakeling <matthew@flymine.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Group by more efficient than distinct?
Date: Tue, 22 Apr 2008 09:04:30 -0400
Message-ID: <480DE25E.4080507@mark.mielke.cc> (raw)
In-Reply-To: <Pine.LNX.4.64.0804221318560.12158@aragorn.flymine.org>
References: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
<87ve2f7bel.fsf@oxford.xeocode.com>
<op.t9sycqo6cigqcu@apollo13.peufeu.com>
<cone.1208704536.428347.85914.1000@zoraida.natserv.net>
<op.t9zif2yocigqcu@apollo13.peufeu.com>
<480D283E.1060602@mark.mielke.cc>
<Pine.LNX.4.64.0804221130190.12158@aragorn.flymine.org>
<480DD390.7020405@mark.mielke.cc>
<Pine.LNX.4.64.0804221318560.12158@aragorn.flymine.org>
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 <mark@mielke.cc>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark@mark.mielke.cc, matthew@flymine.org
Subject: Re: Group by more efficient than distinct?
In-Reply-To: <480DE25E.4080507@mark.mielke.cc>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox