EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT *
FROM user_service_view
WHERE ("UserServiceA"->>'user_ref') ILIKE '%400%'
ORDER BY ("User"->>'userid')
LIMIT 200;


"Limit  (cost=17.38..3428.11 rows=200 width=176) (actual time=9.490..740.152 rows=200.00 loops=1)"
"  Output: u.pkid, u.cache, sva.service_a_json, ((u.cache ->> 'userid'::text))"
"  Buffers: shared hit=467669"
"  ->  Nested Loop  (cost=17.38..17053704.65 rows=1000000 width=176) (actual time=9.485..740.121 rows=200.00 loops=1)"
"        Output: u.pkid, u.cache, sva.service_a_json, (u.cache ->> 'userid'::text)"
"        Buffers: shared hit=467669"
"        ->  Index Scan using idx_demo_user_userid_raw on public.demo_user u  (cost=0.42..68704.65 rows=1000000 width=120) (actual time=0.082..22.218 rows=99338.00 loops=1)"
"              Output: u.pkid, u.scope_id, u.cache"
"              Index Searches: 1"
"              Buffers: shared hit=20636"
"        ->  Subquery Scan on sva  (cost=16.95..16.97 rows=1 width=32) (actual time=0.007..0.007 rows=0.00 loops=99338)"
"              Output: sva.service_a_json, sva_1.pkid"
"              Filter: ((sva.service_a_json ->> 'user_ref'::text) ~~* '%400%'::text)"
"              Rows Removed by Filter: 0"
"              Buffers: shared hit=447033"
"              ->  Limit  (cost=16.95..16.96 rows=1 width=57) (actual time=0.007..0.007 rows=0.50 loops=99338)"
"                    Output: (((jsonb_build_object('__pkid', sva_1.pkid) || sva_1.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), sva_1.pkid"
"                    Buffers: shared hit=447033"
"                    ->  Sort  (cost=16.95..16.96 rows=1 width=57) (actual time=0.007..0.007 rows=0.50 loops=99338)"
"                          Output: (((jsonb_build_object('__pkid', sva_1.pkid) || sva_1.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), sva_1.pkid"
"                          Sort Key: sva_1.pkid"
"                          Sort Method: quicksort  Memory: 25kB"
"                          Buffers: shared hit=447033"
"                          ->  Nested Loop Left Join  (cost=8.89..16.94 rows=1 width=57) (actual time=0.006..0.006 rows=0.50 loops=99338)"
"                                Output: ((jsonb_build_object('__pkid', sva_1.pkid) || sva_1.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)))), sva_1.pkid"
"                                Buffers: shared hit=447033"
"                                ->  Index Scan using idx_demo_user_service_a_user_ref on public.demo_user_service_a sva_1  (cost=0.43..8.45 rows=1 width=72) (actual time=0.003..0.003 rows=0.50 loops=99338)"
"                                      Output: sva_1.pkid, sva_1.scope_id, sva_1.cache"
"                                      Index Cond: (lower((sva_1.cache ->> 'user_ref'::text)) = lower((u.cache ->> 'userid'::text)))"
"                                      Filter: (sva_1.scope_id = u.scope_id)"
"                                      Index Searches: 99338"
"                                      Buffers: shared hit=347685"
"                                ->  Limit  (cost=8.46..8.47 rows=1 width=57) (actual time=0.003..0.003 rows=0.50 loops=49671)"
"                                      Output: ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)), dep.pkid"
"                                      Buffers: shared hit=99348"
"                                      ->  Sort  (cost=8.46..8.47 rows=1 width=57) (actual time=0.003..0.003 rows=0.50 loops=49671)"
"                                            Output: ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)), dep.pkid"
"                                            Sort Key: dep.pkid"
"                                            Sort Method: quicksort  Memory: 25kB"
"                                            Buffers: shared hit=99348"
"                                            ->  Result  (cost=0.42..8.45 rows=1 width=57) (actual time=0.002..0.002 rows=0.50 loops=49671)"
"                                                  Output: (jsonb_build_object('__pkid', dep.pkid) || dep.cache), dep.pkid"
"                                                  One-Time Filter: (sva_1.cache ? 'dependency_ref'::text)"
"                                                  Buffers: shared hit=99348"
"                                                  ->  Index Scan using idx_demo_user_service_a_dep_object_id on public.demo_user_service_a_dependency dep  (cost=0.42..8.45 rows=1 width=90) (actual time=0.003..0.003 rows=1.00 loops=24837)"
"                                                        Output: dep.pkid, dep.scope_id, dep.cache"
"                                                        Index Cond: (lower((dep.cache ->> 'object_id'::text)) = lower((sva_1.cache ->> 'dependency_ref'::text)))"
"                                                        Filter: (dep.scope_id = u.scope_id)"
"                                                        Index Searches: 24837"
"                                                        Buffers: shared hit=99348"
"Planning Time: 1.287 ms"
"Execution Time: 740.338 ms"