Re: minimizing the target list for foreign data wrappers

David Gudeman <dave(dot)gudeman(at)gmail(dot)com> writes:
> One of the problems I'm having is that in my application, the foreign
> tables typically have hundreds of columns while typical queries only access
> a dozen or so (the foreign server is a columnar SQL database). Furthermore,
> there is no size optimization for NULL values passed back from the foreign
> server, so if I return all of the columns from the table --even as NULLs--
> the returned data size will be several times the size that it needs to be.
> My application cannot tolerate this level of inefficiency, so I need to
> return minimal columns from the foreign table.

That's already possible; see contrib/postgres_fdw in HEAD for an
existence proof.

> The documentation doesn't say how to do this, but looking at the code I
> think it is possible. In GetForeignPlan() you have to pass on the tlist
> argument, which I presume means that the query plan will use the tlist that
> I pass in, right? If so, then it should be possible for me to write a
> function that takes tlist and baserel->reltargetlist and return a version
> of tlist that knows which foreign-table columns are actually used, and
> replaces the rest with a NULL constant.

You do not get to editorialize on the tlist that will be computed by the
ForeignScan node: in the case of a simple single-table SELECT, that's
going to be computing the expressions the user asked for. Nor can you
alter the expectation about the rowtype of the scan tuple that's
returned by the FDW: that needs to match the declared rowtype of the
foreign table. However, you can skip fetching unneeded columns and just
set those fields of the scan tuple to nulls. That's cheap enough
(particularly if the scan tuple stays virtual) that I'm unconvinced we
should contort the APIs to the extent that would be needed to let the
FDW change the scan tuple rowtype dynamically.

regards, tom lane