On Thu, Feb 19, 2009 at 1:20 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > [ back to planner stuff after a hiatus ] > > Robert Haas <robertmh...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
