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 по дате отправления: