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

Ed Kurowski <ed(dot)kurowski(at)gmail(dot)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