Re: Group by more efficient than distinct?

[Matthew Wakeling <matthew(at)flymine(dot)org>]

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.

Matthew

--
X's book explains this very well, but, poor bloke, he did the Cambridge Maths 
Tripos...                               -- Computer Science Lecturer