Re: small table left outer join big table

On Wed, Dec 29, 2010 at 3:58 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:

> On Wed, 2010-12-29 at 09:59 -0500, Tom Lane wrote:
> > 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.
>
> Seq scanning the big table isn't bad... we've gone to a lot of trouble
> to make it easy to do this, especially with many users.
>
> Maintaining many large indexes is definitely bad, all that random I/O is
> going to suck badly.
>
> Seems like an interesting and relatively optimisation to me. Not sure if
> this is a request for feature, or a proposal to write the optimisation.
> I hope its the latter.
>
>
Thanks for your comments. Yeah I'm excited to write code for PostgreSQL,
but I'm new here
and not familiar with the code routine or patch submission. I will try to
learn in near future. So
for the moment, it is a request for feature, and I'm looking forward to any
pgsql-hackers working
on this.

Thanks,
Li Jie