Re: Full JSONb column returned over FDW when only single value needed

Testing has shown it improves a lot by removing the data column.  Typically
takes 6 seconds, without the data field, it takes about 10ms.  The 'data'
column here is rather arbitrary and sometimes contains a fair amount of
data.  We considered the view option, but it seems like the planner should
be smart enough to know we only care about a single field and just send
that back instead of all the json.

On Fri, Sep 2, 2016 at 3:06 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ed Kurowski <ed.kurowski@gmail.com> writes:
> > This runs very quickly directly on the remote postgres server (indexes
> have
> > been set appropriately on the foreign server), but is slow when running
> > over the fdw.  I believe I have tracked this down to the fact that it is
> > returning the entire data column (which is jsonb, and sometimes large)
> > instead of the single field from the json my query cares about.
>
> > Is there anyway to stop postgres from returning the entire jsonb column?
>
> This isn't a consideration that postgres_fdw knows anything about at the
> moment.  You could force it by defining a view on the remote server that
> only exposes data->>'query' rather than the whole data column, and making
> the foreign table reference the view not the underlying table.  Of course
> that approach won't scale if there are a lot of different jsonb fields
> you may want to ask about.
>
> FWIW, I'm a little suspicious of whether it'd really help that much,
> though an experiment with the view approach would probably prove or
> disprove things quickly.
>
>                         regards, tom lane
>