Full JSONb column returned over FDW when only single value needed

From: Ed Kurowski <ed(dot)kurowski(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Full JSONb column returned over FDW when only single value needed
Date: 2016-09-02 18:18:34
Message-ID: CANo=kM9y24vPyO=ww1aWVnD7Nc8QmZpgMW4pTN=g1g_wvZs4mw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2016-09-02 19:06:58 Re: Full JSONb column returned over FDW when only single value needed
Previous Message Tom Lane 2016-09-02 14:07:10 Re: BUG #14307: SELECT from pg_timezone_abbrevs gives an ERROR: F0000