Re: Is it possible for postgres_fdw to push down queries on co-located tables?

Jinhua Luo <luajit(dot)io(at)gmail(dot)com> writes:
> That is, if table `foo` and table `bar` are both tables on the same
> remote server, then when I do `select * from foo, bar`, can it
> delegate the whole query on the remote side, rather than fetching rows
> from both servers one by one and do merging on the local side?

Reasonably recent releases can do that. What version are you testing?

> foo=> explain select * from foreign_test2, foreign_test where m = id;
> QUERY PLAN
> -------------------------------------------------------------------------------------
> Merge Join (cost=444.06..590.63 rows=9316 width=72)
> Merge Cond: (foreign_test2.m = foreign_test.id)
> -> Sort (cost=222.03..225.44 rows=1365 width=36)
> Sort Key: foreign_test2.m
> -> Foreign Scan on foreign_test2 (cost=100.00..150.95
> rows=1365 width=36)
> -> Sort (cost=222.03..225.44 rows=1365 width=36)
> Sort Key: foreign_test.id
> -> Foreign Scan on foreign_test (cost=100.00..150.95
> rows=1365 width=36)
> ```

I don't find this particular example to be very compelling. Taking
the amount of data pulled from the foreign server as the main cost
factor, the plan as given requires pulling 1365*2 rows, whereas if
it were to push down the join, it'd have to retrieve 9316 rows
(or so the planner estimates, anyway). So it's quite possible that
the planner just rejected the remote join as a net loss. If you
think it isn't a net loss, you might want to twiddle the cost
parameters for this foreign server.

regards, tom lane