Re: small table left outer join big table

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Wed, Dec 29, 2010 at 7:34 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> It's not a bug, that's the way it currently works. We don't need a test
>> case for that.

> Oh, you're right. I missed the fact that it's a left join.

The only thing that struck me as curious about it was that the OP didn't
get a nestloop-with-inner-indexscan plan. That would be explainable if
there was no index on the large table's "id" column ... but columns
named like that usually have indexes.

I can't get all *that* excited about complicating hash joins as
proposed. The query is still fundamentally going to be slow because
you won't get out of having to seqscan the large table. The only way
to make it really fast is to not read all of the large table, and
nestloop-with-inner-indexscan is the only plan type with a hope of
doing that.

regards, tom lane