SET jit = off;
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
  u.pkid AS "__pkid",
  u.cache AS "User",
  sva.service_a_json AS "UserServiceA"
FROM demo_user u
JOIN LATERAL (
  SELECT
    jsonb_build_object('__pkid', sva.pkid) || sva.cache
    || jsonb_build_object('ServiceADependency', dep.dependency_json) AS service_a_json
  FROM demo_user_service_a sva
  LEFT JOIN LATERAL (
    SELECT jsonb_build_object('__pkid', dep.pkid) || dep.cache AS dependency_json
    FROM demo_user_service_a_dependency dep
    WHERE dep.scope_id = u.scope_id
      AND sva.cache ? 'dependency_ref'
      AND LOWER(dep.cache->>'object_id') = LOWER(sva.cache->>'dependency_ref')
    ORDER BY dep.pkid
    LIMIT 1
  ) dep ON true
  WHERE sva.scope_id = u.scope_id
    AND LOWER(sva.cache->>'user_ref') = LOWER(u.cache->>'userid')
    AND (sva.cache->>'user_ref') ILIKE '%400%'
  ORDER BY sva.pkid
  LIMIT 1
) sva ON true
ORDER BY (u.cache->>'userid')
LIMIT 200;


"Limit  (cost=17.38..3428.12 rows=200 width=176) (actual time=2.016..476.396 rows=200.00 loops=1)"
"  Output: u.pkid, u.cache, (((jsonb_build_object('__pkid', sva.pkid) || sva.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), ((u.cache ->> 'userid'::text))"
"  Buffers: shared hit=368941"
"  ->  Nested Loop  (cost=17.38..17053704.65 rows=1000000 width=176) (actual time=2.015..476.372 rows=200.00 loops=1)"
"        Output: u.pkid, u.cache, (((jsonb_build_object('__pkid', sva.pkid) || sva.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), (u.cache ->> 'userid'::text)"
"        Buffers: shared hit=368941"
"        ->  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.035..16.573 rows=99338.00 loops=1)"
"              Output: u.pkid, u.scope_id, u.cache"
"              Index Searches: 1"
"              Buffers: shared hit=20636"
"        ->  Limit  (cost=16.96..16.96 rows=1 width=57) (actual time=0.004..0.004 rows=0.00 loops=99338)"
"              Output: (((jsonb_build_object('__pkid', sva.pkid) || sva.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), sva.pkid"
"              Buffers: shared hit=348305"
"              ->  Sort  (cost=16.96..16.96 rows=1 width=57) (actual time=0.004..0.004 rows=0.00 loops=99338)"
"                    Output: (((jsonb_build_object('__pkid', sva.pkid) || sva.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache))))), sva.pkid"
"                    Sort Key: sva.pkid"
"                    Sort Method: quicksort  Memory: 25kB"
"                    Buffers: shared hit=348305"
"                    ->  Nested Loop Left Join  (cost=8.89..16.95 rows=1 width=57) (actual time=0.004..0.004 rows=0.00 loops=99338)"
"                          Output: ((jsonb_build_object('__pkid', sva.pkid) || sva.cache) || jsonb_build_object('ServiceADependency', ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)))), sva.pkid"
"                          Buffers: shared hit=348305"
"                          ->  Index Scan using idx_demo_user_service_a_user_ref on public.demo_user_service_a sva  (cost=0.43..8.45 rows=1 width=72) (actual time=0.004..0.004 rows=0.00 loops=99338)"
"                                Output: sva.pkid, sva.scope_id, sva.cache"
"                                Index Cond: (lower((sva.cache ->> 'user_ref'::text)) = lower((u.cache ->> 'userid'::text)))"
"                                Filter: ((sva.scope_id = u.scope_id) AND ((sva.cache ->> 'user_ref'::text) ~~* '%400%'::text))"
"                                Rows Removed by Filter: 0"
"                                Index Searches: 99338"
"                                Buffers: shared hit=347685"
"                          ->  Limit  (cost=8.46..8.47 rows=1 width=57) (actual time=0.012..0.012 rows=0.78 loops=200)"
"                                Output: ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)), dep.pkid"
"                                Buffers: shared hit=620"
"                                ->  Sort  (cost=8.46..8.47 rows=1 width=57) (actual time=0.012..0.012 rows=0.78 loops=200)"
"                                      Output: ((jsonb_build_object('__pkid', dep.pkid) || dep.cache)), dep.pkid"
"                                      Sort Key: dep.pkid"
"                                      Sort Method: quicksort  Memory: 25kB"
"                                      Buffers: shared hit=620"
"                                      ->  Result  (cost=0.42..8.45 rows=1 width=57) (actual time=0.010..0.010 rows=0.78 loops=200)"
"                                            Output: (jsonb_build_object('__pkid', dep.pkid) || dep.cache), dep.pkid"
"                                            One-Time Filter: (sva.cache ? 'dependency_ref'::text)"
"                                            Buffers: shared hit=620"
"                                            ->  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.006..0.006 rows=1.00 loops=155)"
"                                                  Output: dep.pkid, dep.scope_id, dep.cache"
"                                                  Index Cond: (lower((dep.cache ->> 'object_id'::text)) = lower((sva.cache ->> 'dependency_ref'::text)))"
"                                                  Filter: (dep.scope_id = u.scope_id)"
"                                                  Index Searches: 155"
"                                                  Buffers: shared hit=620"
"Planning:"
"  Buffers: shared hit=1"
"Planning Time: 0.410 ms"
"Execution Time: 476.512 ms"