There are a few faster ways (that I know of) to handle the third case with RLS. The first is to make the policy something like this:

CREATE POLICY page_access ON page_photo
  USING(
    page_id = ANY(
      SELECT page_id 
        FROM page_user 
        WHERE page_user.user_id =
          current_setting('app.user_id')::bigint
    )
  );

This takes the running time down from the about ~700ms in the last example you showed to ~35ms. (Those are the timing numbers I got on my machine, which appears to be similar to yours in terms of speed.) It also solves the problem of how to get the user’s page ids into the RSL policy.

Looking at the EXPLAIN ANALYZE for the above shows:

Seq Scan on page_photo  (cost=10000000044.50..10000001823.26 rows=49670 width=12) (actual time=0.080..34.332 rows=118 loops=1)
  Filter: (hashed SubPlan 1)
  Rows Removed by Filter: 99223
  SubPlan 1
    ->  Index Scan using page_user_user_id_idx on page_user  (cost=0.30..44.47 rows=10 width=4) (actual time=0.015..0.029 rows=8 loops=1)
          Index Cond: (user_id = (current_setting('app.user_id'::text))::integer)
Planning time: 0.198 ms
Execution time: 34.398 ms

Honestly, I’m not sure why this is so much faster than your bigint_contains version, since the basic work Pg is doing looks comparable. My only guess is that it’s faster from removing the function call overhead.

Note that, in both my version above and the bigint_contains version, what’s really slowing things down is that Postgres is scanning every row in page_photo (the SeqScan), even though only a tiny number match. It’s doing that because the planner mistakenly estimates that 50% of the rows will match (the rows=49670 above, vs the actual match count of rows=118).

I’m honestly not sure why the planner’s guess is so, so off: it guessed that only 10 rows would be returned from the SELECT sub-query, which was more or less correct, so it should’ve also known that the maximum number of distinct page_id values that the sub-query could yield would is10. Then, it also knows that the rows in page_photo have very diverse page_id values; in my pg_stat table, the most-common value has a frequency of 0.001633 , which implies that the (at most) 10 page_id values from the subquery could yield a maximum of ~100,000*10*.00163, which is ~1,600 rows, which is a far cry from ~49,000. (A more sophisticated estimate, using the average frequency of the top 10 most common page_photo.page_id values would bring the upper-bound estimate down to ~1500.)

I tried a lot of wonkiness to get Postgres to use the index rather than a SeqScan, and many things that seemed like they should’ve worked didn’t. Ultimately, though, I stumbled on this policy:

CREATE POLICY page_access ON page_photo
  USING(
    page_id = ANY(
      ARRAY(
        SELECT page_id 
          FROM page_user 
          WHERE page_user.user_id =
            current_setting('app.user_id')::bigint
      )
    )
  );

It’s completely identical to the one above, except that it explicitly casts the results of the subquery to an array. With that change, the EXPLAIN ANALYZElooks like this:

Bitmap Heap Scan on page_photo (cost=91.98..660.53 rows=995 width=12) (actual time=0.125..0.254 rows=118 loops=1)
 Recheck Cond: (page_id = ANY ($0))
 Heap Blocks: exact=105
 InitPlan 1 (returns $0)
 -> Bitmap Heap Scan on page_user (cost=4.38..41.38 rows=10 width=4) (actual time=0.025..0.036 rows=8 loops=1)
 Recheck Cond: (user_id = (current_setting(‘app.user_id’::text))::integer)
 Heap Blocks: exact=8
 -> Bitmap Index Scan on page_user_user_id_idx (cost=0.00..4.38 rows=10 width=0) (actual time=0.018..0.018 rows=8 loops=1)
 Index Cond: (user_id = (current_setting(‘app.user_id’::text))::integer)
 -> Bitmap Index Scan on page_photo_page_id_idx (cost=0.00..50.35 rows=995 width=0) (actual time=0.105..0.105 rows=118 loops=1)
 Index Cond: (page_id = ANY ($0))
Planning time: 0.193 ms
Execution time: 0.311 ms

This is now running in 1ms, and is structurally similar to the EXPLAIN ANALYZE that I get when using the non-RLS JOIN approach.

My take away from this is that it’s proof that that RLS can (at least usually) be made as performant as direct queries, but it might take a bit of jiggering to get the planner to catch on to things that would otherwise be obvious to it.