Re: New style of hash join proposal

From: Decibel! <decibel(at)decibel(dot)org>
To: Gregory Stark <stark(at)enterprisedb(dot)com>
Cc: pgsql-hackers list <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: New style of hash join proposal
Date: 2007-12-20 00:24:33
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On Dec 13, 2007, at 7:13 AM, Gregory Stark wrote:
> We currently execute a lot of joins as Nested Loops which would be
> more
> efficient if we could batch together all the outer keys and execute
> a single
> inner bitmap index scan for all of them together.
> Essentially what I'm saying is that we're missing a trick with Hash
> Joins
> which currently require that we can execute the inner side once
> without any
> parameters from the outer side.
> Instead what we could do is build up the hash table, then scan the
> hash table
> building up an array of keys and pass them as a parameter to the
> inner side.
> The inner side could do a bitmap index scan to fetch them all at
> once and
> start returning them just as normal to the hash join.
> There are a couple details:
> 1) Batched hash joins. Actually I think this would be fairly
> straightforward.
> You want to rescan the inner side once for each batch. That
> would actually
> be easier than what we currently do with saving tuples to files
> and all
> that.
> 2) How to pass the keys. This could be a bit tricky especially for
> multi-column keys. My first thought was to build up an actually
> Array node
> but that only really works for single-column keys I think.
> Besides it would
> be more efficient to somehow arrange to pass over a reference to
> the whole
> hash.
> I fear the real complexity would be (as always) in the planner
> rather than the
> executor. I haven't really looked into what it would take to
> arrange this or
> how to decide when to do it.

Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! Team #1828

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2007-12-20 00:50:29 Re: pgwin32_open returning EINVAL
Previous Message Dann Corbit 2007-12-19 23:36:44 Re: Sorting Improvements for 8.4