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

Re: small table left outer join big table

From: Jie Li <jay23jack(at)gmail(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: small table left outer join big table
Date: 2010-12-30 01:54:10
Message-ID: AANLkTin2naZLSc8imUXUUP9F9tE=bQh3aBN+3-fOsQTD@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

In response to

Responses

pgsql-hackers by date

Next:From: Greg SmithDate: 2010-12-30 02:39:38
Subject: Re: Re: new patch of MERGE (merge_204) & a question about duplicated ctid
Previous:From: KaiGai KoheiDate: 2010-12-30 01:15:08
Subject: Re: sepgsql contrib module

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