Skip site navigation (1) Skip section navigation (2)

Re: small table left outer join big table

From: Simon Riggs <simon(at)2ndQuadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jie Li <jay23jack(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: small table left outer join big table
Date: 2010-12-29 20:58:18
Message-ID: 1293656298.1892.10087.camel@ebony (view raw or flat)
Thread:
Lists: pgsql-hackers
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.

-- 
 Simon Riggs           http://www.2ndQuadrant.com/books/
 PostgreSQL Development, 24x7 Support, Training and Services
 


In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2010-12-29 21:04:05
Subject: Re: and it's not a bunny rabbit, either
Previous:From: Kevin GrittnerDate: 2010-12-29 20:36:55
Subject: Re: SSI SLRU strategy choices

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group