1

I have a table which contains millions of rows, categorized into a few categories (lets say numbers 1-5). An application that accesses the database uses one single database account. However, the application has its own user accounts and each app user is allowed to access only certain categories. Therefore the list of allowed categories is passed to the database by a session variable:

SET SESSION mydb.allowed_categories = '1,3,5';

I use RLS to filter the rows according to the session variable:

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

The problem is that with this approach the RLS row filtering takes some significant amount of time. On the other hand, when I experimented by doing:

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = 1 OR category = 3 OR category = 5);

the RLS filtering was almost 10 times faster. Of course, such hardcoding is a no go, since I want to change the list of allowed categories dynamically within the app.

The category column has a btree index, however since the number of categories is rather small, the query planner always favors the sequential scan for the RLS filter.

So my question is - is there a way to optimize the RLS expression so it gets at least closer to the hardcoded approach? Would you suggest a different solution to the problem? The application has a lot of users, so I don't want to create a database account for every single one.

  • "since I want to change the list of allowed categories dynamically within the app" - why don't you simply append this to the WHERE clause then? RLS is meant to be constantly changed – a_horse_with_no_name Feb 19 '16 at 22:52
  • 1
    Is there a difference? AFAIK RLS basically appends an extra where clause, it only does it transparently to the user. I think the problem is in writing the expression maybe more efficiently? – NumberFour Feb 19 '16 at 23:23
  • The problem is that you're limited to a single string for input if you're setting things via a GUC. The planner has no visibility into that string at plan-time, so it doesn't have any way to realise that it could do a 3-way bitmap index scan and OR to find matches. You're using the array && operator so you could create a GiST array index to speed it up somewhat, though it won't be as fast as a b-tree search. – Craig Ringer Feb 20 '16 at 11:28
4

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"
"  ->  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[]))"
"  ->  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.

  • Which Postgres version did you use here? I tried your examples with 9.5.12, 9.6 and the latest 10 and which each of that versions already with the first ANY(...) workaround I had great performance. Also later in your first EXPLAIN ANALYZE... example I did not see "Rows Removed by Index" and the query was also very fast. Were there improvements made to Postgres that fixed that performance issues? – mkurz Apr 20 '18 at 17:20
  • Alright, it was my fault, I needed to set SET work_mem='100kB'; to see the slow queries... My default was 4MB and that was too much. – mkurz Apr 20 '18 at 20:45
  • Wow! I did test all your examples and in the end I created the policy with the function: CREATE POLICY table_select_policy ON big_table FOR SELECT USING (category = ANY(my_categories())); Turns out it is unbelievable fast - even more than 10 times faster then CREATE POLICY table_select_policy ON big_table FOR SELECT USING (category = 1 OR category = 3 OR category = 5);! Thank you very much, this is really really great! – mkurz Apr 20 '18 at 22:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.