Performance issue when we use policies for Row Level Security along with functions

Поиск
Список
Период
Сортировка
От Gopisetty, Ramesh
Тема Performance issue when we use policies for Row Level Security along with functions
Дата
Msg-id BL0PR11MB3153082AF1A7B16DEE74DCE5EE210@BL0PR11MB3153.namprd11.prod.outlook.com
обсуждение исходный текст
Ответы Re: Performance issue when we use policies for Row Level Security along with functions  (luis.roberto@siscobra.com.br)
Re: Performance issue when we use policies for Row Level Security along with functions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
Hi,

I'm seeing a strange behavior when we implement policies (for RLS - Row level security)  using functions. 

table test  consists of columns  testkey,oid,category,type,description... 

Policy

create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in (f_sel_policy_test(testkey))  );

Going to a Sequential scan instead of index scan.  Hence, performance issue.

pgwfc01q=> explain analyze select * from test;
                                                 QUERY PLAN
------------------------------------------------------------------------------------------------------------
 Seq Scan on test  (cost=0.00..25713.12 rows=445 width=712) (actual time=1849.592..1849.592 rows=0 loops=1)
   Filter: ((testkey )::text = (f_sel_policy_test(testkey ))::text)
   Rows Removed by Filter: 88930
 Planning Time: 0.414 ms
 Execution Time: 1849.614 ms
(5 rows)


The function is 

CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
Declare
            v_status character varying;
BEGIN

            if vpd_key = 'COMMON' then
                        return '''COMMON''';
            elsif vpd_key = ('COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then
                        return '''COMMON_' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||'''';
            elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then
                        return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||'''';
            end if;
            return 'false';    
            exception when undefined_object then
                        return 'failed';
           
END;
$function$
;


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.

create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

QUERY PLAN

--------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------
 Bitmap Heap Scan on test  (cost=396.66..2966.60 rows=13396 width=712) (actual time=0.693..2.318 rows=13159 loops=1)
   Recheck Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[]))
   Heap Blocks: exact=373
   ->  Bitmap Index Scan on test_pkey  (cost=0.00..393.31 rows=13396 width=0) (actual time=0.653..0.653 rows=13159 l
oops=1)
         Index Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying, (current_setting('ctx_vpd.ctx
_key_fil'::text))::character varying])::text[]))
 Planning Time: 0.136 ms
 Execution Time: 2.843 ms
(7 rows)


If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.   I thought of creating the policy with a lot of business logic in the function.  If i have the function then i notice going for full table scan instead of index. 

Please help me if i miss anything in writing a function or how to use functions in the policy. 

Thank you.


Regards,
Ramesh G

В списке pgsql-performance по дате отправления:

Предыдущее
От: Nagaraj Raj
Дата:
Сообщение: Single column vs composite partial index
Следующее
От: Justin Pryzby
Дата:
Сообщение: Re: Single column vs composite partial index