Re: pass-through queries to foreign servers

On Tue, Jul 30, 2013 at 04:40:38PM -0700, David Gudeman wrote:
> When you write an application involving foreign tables, you frequently
> end up with queries that are just too inefficient because they bring
> too much data over from the foreign server. For a trivial example,
> consider "SELECT count(*) FROM t" where t is a foreign table. This
> will pull the entire table over the network just to count up the rows.
> If the writer of the foreign data wrapper was clever enough, this may
> only pull one column from the foreign server, but that can still be a
> lot of data.

Yes, and this case is a known limitation of our planner
infrastructure. Aggregates are "special" when it comes to
generating paths for the planner to evaluate, so there's no current
way a FDW could supply such info to the planner, and hence no API in
our FDW code for having FDWs supply that info. That's probably a
"should fix" but I don't know whether a project that size could be
done by 9.4.

All that said, my DBI-Link, back in the bad old days, provided two
important functions: remote_select(), which returned SETOF RECORD and
remote_execute(), which returned nothing. It also provided ways to
control connections to the remote host, introspect remote schemas,
etc., etc. We need capabilities like that in the FDW API, I believe
we could have them by 9.4.

I don't know how to solve your problem within the context of our
current FDW API, but I think it's common enough that we do need to
solve it as above.

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate