pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance issue when we use policies for Row Level Security along with functions
5+ messages / 4 participants
[nested] [flat]
* Performance issue when we use policies for Row Level Security along with functions
@ 2020-09-16 03:39 Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-09-16 10:52 ` Re: Performance issue when we use policies for Row Level Security along with functions luis.roberto@siscobra.com.br
2020-09-16 14:17 ` Re: Performance issue when we use policies for Row Level Security along with functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 5+ messages in thread
From: Gopisetty, Ramesh @ 2020-09-16 03:39 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue when we use policies for Row Level Security along with functions
2020-09-16 03:39 Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
@ 2020-09-16 10:52 ` luis.roberto@siscobra.com.br
1 sibling, 0 replies; 5+ messages in thread
From: luis.roberto@siscobra.com.br @ 2020-09-16 10:52 UTC (permalink / raw)
To: Gopisetty, Ramesh <rameshg2@illinois.edu>; +Cc: pgsql-performance <pgsql-performance@lists.postgresql.org>
De: "Gopisetty, Ramesh" <rameshg2@illinois.edu>
Para: "pgsql-performance" <pgsql-performance@lists.postgresql.org>
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08
Assunto: Performance issue when we use policies for Row Level Security along with functions
BQ_BEGIN
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
BQ_END
You could try seeting the function as immutable. By default it is volatile.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue when we use policies for Row Level Security along with functions
2020-09-16 03:39 Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
@ 2020-09-16 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-12 06:46 ` Re: Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2020-09-16 14:17 UTC (permalink / raw)
To: Gopisetty, Ramesh <rameshg2@illinois.edu>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
"Gopisetty, Ramesh" <rameshg2@illinois.edu> writes:
> 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.
> 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')));
" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).
" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition. So there's no fixed value that the
index can search on.
If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?
As Luis noted, there's also the problem that an indexable condition
can't be volatile. I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance issue when we use policies for Row Level Security along with functions
2020-09-16 03:39 Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-09-16 14:17 ` Re: Performance issue when we use policies for Row Level Security along with functions Tom Lane <tgl@sss.pgh.pa.us>
@ 2020-10-12 06:46 ` Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-10-12 07:26 ` Performance issue when we use policies for Row Level Security along with functions David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Gopisetty, Ramesh @ 2020-10-12 06:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; luis.roberto@siscobra.com.br <luis.roberto@siscobra.com.br>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Hi,
Thanks for providing the details. But things didn't work out even after changing the functions to STABLE/IMMUTABLE. If i don't use the function it works for RLS. If i use functions it doesn't work.
I tried with both IMMUTABLE and STABLE. Both didn't work. Is there a way to use function in RLS to have the index scan rather than the seq scan. Please help me out if that works or not.
Currently, we are in the processes of converting oracle to postgres. Under oracle we have used functions and there exists a lot of logic in it.
Thank you.
Function
drop function f_sel_1;
CREATE OR REPLACE FUNCTION f_sel_1(key character varying)
RETURNS character varying
LANGUAGE plpgsql
IMMUTABLE
AS $function$
Declare
v_status boolean;
key_ctx varchar(4000);
BEGIN
SELECT INTO key_ctx current_setting('key_header' || '.'||'ctx_key_fil');
if key = key_ctx then
return key_ctx;
end if;
return '';
exception when undefined_object then
return '';
END;
$function$
;
drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING ( key =
f_sel_1(key)
);
explain analyze select * from test1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------
Seq Scan on test1 (cost=0.00..1555.61 rows=25 width=555) (actual time=35.124..35.124 rows=0 loops=1)
Filter: ((key)::text = (f_sel_1(key))::text)
Rows Removed by Filter: 4909
Planning Time: 0.070 ms
Execution Time: 35.142 ms
(5 rows)
drop policy policy_sel on test1;
create policy policy_sel on test1 FOR
SELECT
to sch USING (
key =
(
current_setting('key_header'|| '.' || 'ctx_key_fil')
)
);
explain analyze select * from test1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test1 (cost=9.78..270.01 rows=193 width=555) (actual time=0.040..0.069 rows=193 loops=1)
Recheck Cond: ((key)::text = current_setting('key_header.ctx_key_fil'::text))
Heap Blocks: exact=13
-> Bitmap Index Scan on test1_pkey (cost=0.00..9.73 rows=193 width=0) (actual time=0.030..0.030 rows=193 loops=1)
Index Cond: ((key)::text = current_setting('key_header.ctx_key_fil'::text))
Planning Time: 0.118 ms
Execution Time: 0.094 ms
(7 rows)
CREATE TABLE sch.test1 (
key varchar(50) NOT NULL,
id varchar(32) NOT NULL,
begin_date date NOT NULL,
eff_date_end date NULL,
code varchar(100) NULL,
CONSTRAINT test1_pkey PRIMARY KEY (vpd_key, id, begin_date)
);
Thank you.
Regards,
Ramesh G
________________________________
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Wednesday, September 16, 2020 10:17 AM
To: Gopisetty, Ramesh <rameshg2@illinois.edu>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance issue when we use policies for Row Level Security along with functions
"Gopisetty, Ramesh" <rameshg2@illinois.edu> writes:
> 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.
> 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')));
" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).
" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition. So there's no fixed value that the
index can search on.
If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?
As Luis noted, there's also the problem that an indexable condition
can't be volatile. I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Performance issue when we use policies for Row Level Security along with functions
2020-09-16 03:39 Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-09-16 14:17 ` Re: Performance issue when we use policies for Row Level Security along with functions Tom Lane <tgl@sss.pgh.pa.us>
2020-10-12 06:46 ` Re: Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
@ 2020-10-12 07:26 ` David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2020-10-12 07:26 UTC (permalink / raw)
To: Gopisetty, Ramesh <rameshg2@illinois.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; luis.roberto@siscobra.com.br <luis.roberto@siscobra.com.br>
On Sunday, October 11, 2020, Gopisetty, Ramesh <rameshg2@illinois.edu>
wrote:
>
> to sch USING ( key =
> f_sel_1(key)
> );
>
As Tom said it doesn’t matter what you classify the function as (stable,
etc) if your function call accepts a column reference as an input and
compares its output to another column reference. With a column reference
you need a row to find a value and if you already have a row the index
serves no purpose.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2020-10-12 07:26 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-09-16 03:39 Performance issue when we use policies for Row Level Security along with functions Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-09-16 10:52 ` luis.roberto@siscobra.com.br
2020-09-16 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2020-10-12 06:46 ` Gopisetty, Ramesh <rameshg2@illinois.edu>
2020-10-12 07:26 ` David G. Johnston <david.g.johnston@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox