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. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
