On Mon, Apr 16, 2012 at 7:05 PM, Tom Lane <t...@sss.pgh.pa.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
