Re: pass-through queries to foreign servers - Mailing list pgsql-hackers

From David Fetter
Subject Re: pass-through queries to foreign servers
Date
Msg-id 20130731003318.GA14716@fetter.org
Whole thread Raw
In response to pass-through queries to foreign servers  (David Gudeman <dave.gudeman@gmail.com>)
Responses Re: pass-through queries to foreign servers
List pgsql-hackers
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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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



pgsql-hackers by date:

Previous
From: David Gudeman
Date:
Subject: pass-through queries to foreign servers
Next
From: Amit Langote
Date:
Subject: Re: Computer VARSIZE_ANY(PTR) during debugging