Re: Fixing Grittner's planner issues

On Thu, Feb 19, 2009 at 1:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> [ back to planner stuff after a hiatus ]
>
> Robert Haas <robertmhaas@gmail.com> writes:
>> Right, so maybe I wasn't as clear as I could have been in asking the
>> question.  I do understand how it can be a win to unique B and use it
>> as the OUTER relation (jointype JOIN_UNIQUE_OUTER).  What I don't
>> understand is how it can ever be a win to unique B and use it as the
>> INNER relation (jointype JOIN_UNIQUE_INNER).
>
> Hmm, well, maybe B is *really* nonunique and unique'ifying it makes it
> small enough to fit in a single-batch hash table?
>
> Also, seriously nonunique RHS data is pretty awful for mergejoining
> (too much rescanning) so I could imagine wanting to do it for a
> mergejoin too.

Well, as I wrote upthread:

# For a merge join or nested loop, I don't see how this can ever be a
win over teaching the executor to just not rescan B.  For a hash
# join, it can be a win if B turns out to have duplicates, but then
again you could also just teach the executor to skip the insertion of
# the duplicate into the table in the first place (it has to hash 'em
anyway...).

A nestjoin seems like the clearest example.  If the inner path is not
unique and not sorted, you'll need to either sort it or hash it.  That
seems like a lot of trouble considering that you could just scan the
unsorted inner path until you hit the first match, and then move on to
the next outer tuple (and I think this is pretty much what JOIN_SEMI
does anyway).

If the inner path is not unique but does happen to be sorted, then
unique-ifying should be cheap, but I would think it would still be
faster to do it the JOIN_SEMI way rather than insert a separate unique
node to do basically the same work.

If add a materialize node for the inner path after you unique-ify it,
you might reduce the number of tuples by enough to pay for the
unique-ify and materialize steps, but if that's the case you should
probably be doing it that way for JOIN_SEMI too.

...Robert