Re: WIP: Join push-down for foreign tables

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
Cc: Shigeru Hanada <shigeru(dot)hanada(at)gmail(dot)com>, Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>, Robert Haas <robertmhaas(at)gmail(dot)com>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: WIP: Join push-down for foreign tables
Date: 2011-12-02 22:24:40
Message-ID: 25686.1322864680@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> On 02.12.2011 18:55, Tom Lane wrote:
>> Furthermore, you seem to be imagining that there is only one best path
>> for any join, which isn't the case.

> No, I understand that the planner considers many alternatives, even at
> the same time, because of different output sort orders and startup vs.
> total cost. I'm imagining that the planner would ask the FDW to
> construct the two-way joins, and consider joining the results of those
> locally to the third table, and also ask the FDW to construct the
> three-way join as whole. And then choose the cheapest alternative.

It probably makes sense to turn control over to the FDW just once to
consider all possible foreign join types for a given join pair, ie
we don't want to ask it separately about nestloop, hash, merge joins.
But then we had better be able to let it generate multiple paths within
the one call, and dump them all to add_path. You're still assuming that
there is one unique best path for any join, and *that is not the case*,
or at least we don't know which will be the best at the time we're
generating join paths. We don't know whether fast-start is better than
cheapest-total, nor which sort order might be the best, until we get up
to the highest join level.

So rather than returning a Path struct, it would have to just be given
the joinrel struct and be expected to do add_path call(s) for itself.

> I don't understand why the FDW should care about the order the joins are
> constructed in in the planner. From the FDW's point of view, there's no
> difference between joining ((A B) C) and (A (B C)).

Maybe there is, maybe there isn't. You're assuming too much about how
the FDW does its join planning, I think --- in particular, FDWs that are
backed by less than a Postgres-equivalent remote planner might well
appreciate being walked through all the feasible join pairs.

If we do it as I suggest above, the FDW could remember that it had
already planned this joinrel and just drop out immediately if called
again, if it's going to do it the way you're thinking.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2011-12-02 22:57:54 Re: WIP: Join push-down for foreign tables
Previous Message Robert Haas 2011-12-02 22:20:05 clog buffers (and FlexLocks)