Query:
SELECT user_id, DATA->>'query' AS query FROM aggregates WHERE guid LIKE
'search-%' AND time >= '2016-01-01' AND time < '2017-01-01' AND
organization_id = 23;
Explain (analyze, verbose):
Foreign Scan on public.aggregates (cost=1184.50..29250.63 rows=23379
width=816) (actual time=17.497..5657.981 rows=18944 loops=1)
Output: user_id, (data ->> 'query'::text)
Remote SQL: SELECT user_id, data FROM public.aggregates WHERE ((guid ~~
'search-%'::text)) AND (("time" >= '2016-01-01 00:00:00'::timestamp without
time zone)) AND (("time" < '2017-01-01 00:00:00'::timestamp without time
zone)) AND ((organization_id = 23))
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?
-Ed
