Re: minimizing the target list for foreign data wrappers - Mailing list pgsql-hackers

Re-reading my first email I thought it was a little confusing, so here
is some clarification. In GetForeignPlan, tlist seems to be a target
list for a basic "select *" from the foreign table. For the ith
TargetEntry te in tlist, it seems that te->expr is a var with
varattno=i. I was mis-remembering and calling varattno "attrno" in the
original email.

My assumption is that the plan elements that use the output of the FDW
plan node will access columns indirectly using tlist. In other words,
I'm assuming that if there is a reference to a column c of the foreign
table, this column will be represented as a Var with varattno being an
offset into tlist. So if c is column number 3, for example, you get
its value by looking up TargetEntry number 3 in tlist and evaluate the
expr column for that TargetEntry. So if I change the  Var in the expr
column so the varattno points to a different column in the output
tuple, then everything will work.

The two risky assumptions I'm making are 1. that it actually uses this
indirect way of looking up columns in a foreign table and 2. that it
actually uses the tlist that I pass in when I call make_foreignscan().

Can anyone confirm or deny these assumptions?

Thanks.

On Sun, Apr 21, 2013 at 6:57 PM, David Gudeman <dave.gudeman@gmail.com> wrote:
>
> A few years ago I wrote a roll-your-own foreign-data-wrapper system for Postgres because Postgres didn't have one at
thetime (some details here (http://unobtainabol.blogspot.com/2013/04/dave-foreign-data-introuction.html) if anyone is
interested).Now I'm being tasked to move it to Postgres 9.2.x and I'd like to use FDW if possible. 
>
> One of the problems I'm having is that in my application, the foreign tables typically have hundreds of columns while
typicalqueries only access a dozen or so (the foreign server is a columnar SQL database). Furthermore, there is no size
optimizationfor NULL values passed back from the foreign server, so if I return all of the columns from the table
--evenas NULLs-- the returned data size will be several times the size that it needs to be. My application cannot
toleratethis level of inefficiency, so I need to return minimal columns from the foreign table. 
>
> The documentation doesn't say how to do this, but looking at the code I think it is possible. In GetForeignPlan() you
haveto pass on the tlist argument, which I presume means that the query plan will use the tlist that I pass in, right?
Ifso, then it should be possible for me to write a function that takes tlist and baserel->reltargetlist and return a
versionof tlist that knows which foreign-table columns are actually used, and replaces the rest with a NULL constant. 
>
> For example, suppose the original tlist is this: [VAR(attrno=1), VAR(attrno=2), VAR(attrno=3)] and reltarget list
saysthat I only need args 1 and 3. Then the new tlist would look like this: [VAR(attrno=1), CONST(val=NULL),
VAR(attrno=2)]where the attrno of the last VAR has been reduced by one because the 2 column is no longer there. 
>
> I did something very much like this in my roll-your-own version of FDW so I know basically how to do it, but I did it
atthe pre-planning stage and I'm not sure how much is already packed into the other plan nodes at this point. Maybe
it'stoo late to change the target list? 
>
> Can anyone give me some advice or warnings on this? I'd hate to go to the trouble of implementing and testing it only
tofind that I'm making some bogus assumptions. 
>
> Thanks,
> David Gudeman
>