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 08:01:20 -0400
Message-ID: <480DD390.7020405@mark.mielke.cc> (raw)
In-Reply-To: <Pine.LNX.4.64.0804221130190.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>
Matthew Wakeling wrote:
> On Mon, 21 Apr 2008, Mark Mielke wrote:
>> This surprises me - hash values are lossy, so it must still need to
>> confirm against the real list of values, which at a minimum should
>> require references to the rows to check against?
>>
>> Is PostgreSQL doing something beyond my imagination? :-)
>
> Not too far beyond your imagination, I hope.
>
> It's simply your assumption that the hash table is lossy. Sure, hash
> values are lossy, but a hash table isn't. Postgres stores in memory
> not only the hash values, but the rows they refer to as well, having
> checked them all on disc beforehand. That way, it doesn't need to look
> up anything on disc for that branch of the join again, and it has a
> rapid in-memory lookup for each row.
I said hash *values* are lossy. I did not say hash table is lossy.
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).
Cheers,
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: <480DD390.7020405@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