Re: 9.3 Pre-proposal: Range Merge Join

On Mon, Apr 16, 2012 at 7:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm. This sounds like something that Tom's recent work on
>> parameterized plans ought to have fixed, or if not, it seems closely
>> related.
>
> Not really.  It's still going to be a nestloop, and as such not terribly
> well suited for queries where there are a lot of matchable rows on both
> sides.  The work I've been doing is really about making nestloops usable
> in cases where join order restrictions formerly prevented it --- but
> Jeff's complaint has nothing to do with that.  (This thought also makes
> me a bit dubious about the nearby suggestions that more indexes will
> fix it.)

I thought Jeff was parenthetically complaining about cases like A LEFT
JOIN (B INNER JOIN C ON b.y = c.y) ON a.x && b.x. That presumably
would require the parameterized-path stuff to have any chance of doing
partial index scans over B. However, I understand that's not the main
issue here.

One thing that I think needs some analysis is when the range join idea
is better or worse than a nested loop with inner index-scan, because
potentially those are the options the planner has to choose between,
and the costing model had better know enough to make the right thing
happen. It strikes me that the nested loop with inner index-scan is
likely to be a win when there are large chunks of the indexed relation
that the nestloop never needs to visit at all - imagine small JOIN big
ON small.a && big.a, for example. I suppose the really interesting
question is how much we can save when the entirety of both relations
has to be visited anyway - it seems promising, but I guess we won't
know for sure without testing it.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company