On Wed, Dec 29, 2010 at 3:58 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> On Wed, 2010-12-29 at 09:59 -0500, Tom Lane wrote: > > Robert Haas <robertmh...@gmail.com> writes: > > > On Wed, Dec 29, 2010 at 7:34 AM, Simon Riggs <si...@2ndquadrant.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
