Full JSONb column returned over FDW when only single value needed

Поиск
Список
Период
Сортировка
От Ed Kurowski
Тема Full JSONb column returned over FDW when only single value needed
Дата
Msg-id CANo=kM9y24vPyO=ww1aWVnD7Nc8QmZpgMW4pTN=g1g_wvZs4mw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Full JSONb column returned over FDW when only single value needed
Список 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

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: BUG #14307: SELECT from pg_timezone_abbrevs gives an ERROR: F0000
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Full JSONb column returned over FDW when only single value needed