Optimize Row Level Security expression in Postgres
At first glance, it might appear that since current_setting and string_to_array functions are stable and immutable respectively and there is an index on the category column, the following condition could do the trick.

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = ANY(string_to_array(current_setting('mydb.allowed_categories'),',')::int[])));


However, the real problem has nothing to do with the form of the condition. If you take a look at the PostgreSQL system catalog and find those two functions that were used in the condition, you may notice that both functions have their cost parameter set to 1. This makes the optimizer assume that calling those functions to recheck the condition while doing Bitmap Heap Scan is cheap.

In order to illustrate this, consider the big_table table shown below.

CREATE TABLE big_table AS
SELECT c AS category
FROM generate_series(1, 1000000) g,
     generate_series(1, 10) c;

CREATE INDEX big_table_category_idx ON big_table (category);

ANALYZE big_table;


Querying the table with the the query below results in the following plan (look at the number of rows removed by Index Recheck).

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);

"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=166.613..9273.010 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  Rows Removed by Index Recheck: 5209365"
"  -&gt;  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=164.782..164.782 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 9341.568 ms"


To avoid that, you have two options. The first is to increase the work_mem parameter in your server config. This will allow the server to store the complete bitmap in memory and save a great deal of time while rechecking the condition. The plan below was obtained when the work_mem parameter was set to 1000M.

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);


"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=193.613..449.385 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  -&gt;  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=184.858..184.858 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 513.528 ms"


The second option is to wrap the query condition in a "costly" function.

CREATE OR REPLACE FUNCTION my_categories()
RETURNS int[] AS $$
BEGIN
  RETURN string_to_array(current_setting('mydb.allowed_categories'), ',')::int[];
END;
$$ LANGUAGE plpgsql STABLE COST 100000;

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(my_categories());

"Index Only Scan using big_table_category_idx on big_table  (cost=250.44..9363945.19 rows=3036665 width=4) (actual time=0.035..577.094 rows=3000000 loops=1)"
"  Index Cond: (category = ANY (my_categories()))"
"  Heap Fetches: 3000000"
"Total runtime: 642.522 ms"


This option will also make the optimizer end up performing Index Scan instead of Bitmap Scan which may turn out to be a bit slower.