Re: Research/Implementation of Nested Loop Join optimization

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Manoel Henrique" <mhenriquesgbd(at)gmail(dot)com>
Cc: "Dann Corbit" <DCorbit(at)connx(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Research/Implementation of Nested Loop Join optimization
Date: 2008-07-24 05:49:15
Message-ID: 20189.1216878555@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

"Manoel Henrique" <mhenriquesgbd(at)gmail(dot)com> writes:
> The nodeMergejoin.c is the code for the Merge Join isn`t it? I am trying to
> find a way to change the Nested Loop Join, It would be more like on
> nodeNestloop.c when rescanning the inner plan, (second time scanning the
> inner plan and so on) he`d change the scan direction, If the scan direction
> was from first tuple to last tuple it would go backwards, if it was from
> last to first it would go forward... The code I`m looking atm is from 8.3.1
> , seems to have some kind of direction manager but doesn`t seems to be in
> use.

I find this a bit dubious. If the inner rel is small enough to fit in
memory then it buys nothing. If not, then you win only to the extent
that a pretty large fraction of the inner rel fits in memory. In any
case you are relying on the assumption that backwards scan is just as
efficient as forward scan, which seems to me to be a pretty large
assumption --- we expect forward seqscans to get a performance boost
from kernel readahead, but I'd be surprised if the kernel recognized
what was happening in a backwards scan.

Note also that backwards scan doesn't work at all in some plan
node types (cf ExecSupportsBackwardScan). You'd need to check
what the inner input node was before trying this.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2008-07-24 06:05:38 Re: issues/experience with building postgres on Windows
Previous Message Tatsuo Ishii 2008-07-24 04:55:37 Re: [PATCHES] WITH RECUSIVE patches 0723