Menu
I have changed STABLE to IMMUTABLE and expecting it to work faster, but it works more slower. Did I miss something?
IMMUTABLE function:
test=> \sf+ rls_guard
CREATE OR REPLACE FUNCTION public.rls_guard()
RETURNS TABLE(org_id integer, unit_id integer, user_id integer)
LANGUAGE sql
IMMUTABLE
1 AS $function$
2 SELECT
3 app_config( 'org_id' )::int,
4 app_config( 'unit_id' )::int,
5 app_config( 'user_id' )::int;
6 $function$
test=> begin;
BEGIN
Time: 0,306 ms
test=*> SELECT app_config( '{"user_id":3, "org_id":1, "unit_id":2 }'::jsonb);
app_config
------------
(1 row)
Time: 0,670 ms
test=*> select * from rls_test;
id | org_id | unit_id | user_id
----+--------+---------+---------
5 | 1 | 2 | 3
(1 row)
Time: 1,038 ms
test=*> DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM rls_guard();
END LOOP;
END $$;
DO
Time: 26464,831 ms (00:26,465)
test=*> DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM * FROM rls_test;
END LOOP;
END $$;
DO
Time: 29063,969 ms (00:29,064)
STABLE function:
test=> \sf+ rls_guard
CREATE OR REPLACE FUNCTION public.rls_guard()
RETURNS TABLE(org_id integer, unit_id integer, user_id integer)
LANGUAGE sql
STABLE
1 AS $function$
2 SELECT
3 app_config( 'org_id' )::int,
4 app_config( 'unit_id' )::int,
5 app_config( 'user_id' )::int;
6 $function$
test=> begin;
BEGIN
Time: 0,320 ms
test=*> SELECT app_config( '{"user_id":3, "org_id":1, "unit_id":2 }'::jsonb);
app_config
------------
(1 row)
Time: 0,734 ms
test=*> select * from rls_test;
id | org_id | unit_id | user_id
----+--------+---------+---------
5 | 1 | 2 | 3
(1 row)
Time: 1,007 ms
test=*> DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM rls_guard();
END LOOP;
END $$;
DO
Time: 20729,006 ms (00:20,729)
test=*> DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM * FROM rls_test;
END LOOP;
END $$;
DO
Time: 23626,823 ms (00:23,627)
UPD
As requested here it is app_config:
app_config(text) | CREATE OR REPLACE FUNCTION public.app_config(key text) >
| RETURNS text >
| LANGUAGE sql >
| STABLE >
| AS $function$ >
| SELECT NULLIF( current_setting( 'app.' || key, true ), '' ); >
| $function$ >
During the test both functions were switched to IMMUTABLE and back.
I am sorry, it seems this is most important part I missed: How rls_guard and rls_test belongs to each other.
CREATE TABLE rls_test (
id SERIAL,
org_id INT NOT NULL DEFAULT current_setting( 'app.org_id', true )::int,
unit_id INT NOT NULL DEFAULT current_setting( 'app.unit_id', true )::int,
user_id INT NOT NULL DEFAULT current_setting( 'app.user_id', true )::int
);
-- TODO: add uniq constraint
INSERT INTO rls_test ( org_id, unit_id, user_id ) VALUES
( 1, 1, 1 ),
( 1, 1, 2 ),
( 1, 1, 3 ),
( 1, 2, 1 ),
( 1, 2, 3 ),
( 1, 3, 2 ),
( 2, 1, 1 );
ALTER TABLE rls_test FORCE ROW LEVEL SECURITY;
ALTER TABLE rls_test ENABLE ROW LEVEL SECURITY;
-- If table has RESTRICTIVE rules it must have at least one permissive rule
-- https://www.postgresql.org/docs/current/sql-createpolicy.html#id-1.9.3.75.6
CREATE POLICY db_tenant_allow_any ON rls_test USING( true );
CREATE POLICY db_tenant_by ON rls_test AS RESTRICTIVE
USING( (org_id, unit_id, user_id) = (select (rls_guard()).*) );
So when we select * from rls_test implicit call to rls_guard is done.
Under root account where it passes RLS the picture looks like this:
(IMMUTABLE case):
test=# DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM rls_guard();
END LOOP;
END $$;
DO
Time: 23648,919 ms (00:23,649)
test=# DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM * FROM rls_test;
END LOOP;
END $$;
DO
Time: 1134,391 ms (00:01,134)
STABLE case:
test=# DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM rls_guard();
END LOOP;
END $$;
DO
Time: 20000,007 ms (00:20,000)
test=# DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM * FROM rls_test;
END LOOP;
END $$;
DO
Time: 1140,360 ms (00:01,140)
Adding EXPLAIN ANALYSE:
test=*# explain ( analyse, verbose, costs, settings, timing, summary, buffers) select rls_guard();
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=31.094..31.114 rows=1 loops=1)
Output: rls_guard()
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=3.538..3.539 rows=1 loops=1)
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.033 ms
JIT:
Functions: 1
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.097 ms (Deform 0.000 ms), Inlining 0.015 ms, Optimization 0.808 ms, Emission 2.704 ms, Total 3.624 ms
Execution Time: 31.249 ms
(10 rows)
Time: 31,561 ms
test=*# explain ( analyse, verbose, costs, settings, timing, summary, buffers) select * from rls_test;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on public.rls_test (cost=0.00..28.50 rows=1850 width=16) (actual time=0.013..0.015 rows=7 loops=1)
Output: id, org_id, unit_id, user_id
Buffers: shared hit=1
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.047 ms
Execution Time: 0.044 ms
(6 rows)
Time: 0,408 ms
and regular account:
IMMUTABLE case:
test=*> explain ( analyse, verbose, costs, settings, timing, summary, buffers) select rls_guard();
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=64.470..64.483 rows=1 loops=1)
Output: rls_guard()
Buffers: shared hit=6
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=3.169..3.169 rows=1 loops=1)
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.029 ms
JIT:
Functions: 1
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.086 ms (Deform 0.000 ms), Inlining 0.014 ms, Optimization 0.813 ms, Emission 2.335 ms, Total 3.248 ms
Execution Time: 64.602 ms
(11 rows)
Time: 65,002 ms
test=*> explain ( analyse, verbose, costs, settings, timing, summary, buffers) select * from rls_test;
QUERY PLAN >
------------------------------------------------------------------------------------------------------------------------------------->
Seq Scan on public.rls_test (cost=765.26..807.64 rows=1 width=16) (actual time=72.676..72.678 rows=1 loops=1)
Output: rls_test.id, rls_test.org_id, rls_test.unit_id, rls_test.user_id
Filter: ((rls_test.org_id = (InitPlan 1).col1) AND (rls_test.unit_id = (InitPlan 1).col2) AND (rls_test.user_id = (InitPlan 1).col>
Rows Removed by Filter: 6
Buffers: shared hit=1
InitPlan 1
-> Result (cost=0.00..765.26 rows=1000 width=12) (actual time=59.178..59.191 rows=1 loops=1)
Output: ((rls_guard())).org_id, ((rls_guard())).unit_id, ((rls_guard())).user_id
-> ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=59.171..59.184 rows=1 loops=1)
Output: rls_guard()
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.080 ms
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.329 ms (Deform 0.075 ms), Inlining 4.316 ms, Optimization 4.535 ms, Emission 4.622 ms, Total 13.802 ms
Execution Time: 73.047 ms
(18 rows)
Time: 73,507 ms
STABLE case:
explain ( analyse, verbose, costs, settings, timing, summary, buffers) select rls_guard();
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=30.851..30.872 rows=1 loops=1)
Output: rls_guard()
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=3.987..3.988 rows=1 loops=1)
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.031 ms
JIT:
Functions: 1
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.099 ms (Deform 0.000 ms), Inlining 0.017 ms, Optimization 1.071 ms, Emission 2.893 ms, Total 4.080 ms
Execution Time: 31.007 ms
(10 rows)
Time: 31,406 ms
explain ( analyse, verbose, costs, settings, timing, summary, buffers) select * from rls_test;
QUERY PLAN >
------------------------------------------------------------------------------------------------------------------------------------->
Seq Scan on public.rls_test (cost=765.26..807.64 rows=1 width=16) (actual time=40.513..40.516 rows=1 loops=1)
Output: rls_test.id, rls_test.org_id, rls_test.unit_id, rls_test.user_id
Filter: ((rls_test.org_id = (InitPlan 1).col1) AND (rls_test.unit_id = (InitPlan 1).col2) AND (rls_test.user_id = (InitPlan 1).col>
Rows Removed by Filter: 6
Buffers: shared hit=1
InitPlan 1
-> Result (cost=0.00..765.26 rows=1000 width=12) (actual time=24.662..24.679 rows=1 loops=1)
Output: ((rls_guard())).org_id, ((rls_guard())).unit_id, ((rls_guard())).user_id
-> ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=24.652..24.669 rows=1 loops=1)
Output: rls_guard()
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.005..0.005 rows=1 loops=1)
Settings: jit_above_cost = '0', jit_inline_above_cost = '0', jit_optimize_above_cost = '0'
Planning Time: 0.086 ms
JIT:
Functions: 4
Options: Inlining true, Optimization true, Expressions true, Deforming true
Timing: Generation 0.354 ms (Deform 0.072 ms), Inlining 5.612 ms, Optimization 5.013 ms, Emission 5.199 ms, Total 16.178 ms
Execution Time: 40.913 ms
(18 rows)
From the above we can see that for STABLE starting from here it works twice faster: STABLE: -> ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=24.652..24.669 rows=1 loops=1) IMMUTA: -> ProjectSet (cost=0.00..5.27 rows=1000 width=32) (actual time=59.171..59.184 rows=1 loops=1)
ST:24 VS IM:59
I am just guessing that IMMUTABLE functions work a bit in a different way in compare to STABLE when Row Level Security is in effect.
Side note: After
SET jit = on;
SET jit_above_cost = 0;
SET jit_inline_above_cost = 0;
SET jit_optimize_above_cost = 0;
the
DO $$
DECLARE
i int;
BEGIN
FOR i IN 1..1000000 LOOP
PERFORM rls_guard();
END LOOP;
END $$;
works 1000 times slower!
Results are pretty stable. I run any measurement multiple times. My test env does not run any heavy task in background. And the same results I see today after my PC was rebooted. I do not think this is a noise.
Btw.
select version();
version
-----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 17.6 (Ubuntu 17.6-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 13.3.0-6ubuntu2~24.04) 13.3.0, 64-bit
(1 row)
695
I think so your design of RLS is extra expensive (and extra creative). You can try (it can be worse too), sometimes is difficult to design concept that works with all optimizations, operators and rows (composite expansions).
The app_config can be relatively slow function, so you really want to block repeated calling of this function for every row (without optimization). Because result depends on configuration, correct mark for this function should be STABLE. Second question is if this function for good performance doesn't need to flag LEAKPROOF
CREATE FUNCTION public.rls_guard(org_id int, unit_id int, user_id int)
RETURNS boolean STABLE
LANGUAGE sql AS $$
SELECT app_config( 'org_id' )::int = org_id
AND app_config( 'unit_id' )::int = unit_id
AND app_config( 'user_id' )::int = user_id;
$$;
-- or maybe
CREATE FUNCTION public.rls_guard2(org_id int, unit_id int, user_id int)
RETURNS boolean STABLE
LANGUAGE sql AS $$
SELECT (app_config( 'org_id' )::int,
app_config( 'unit_id' )::int,
app_config( 'user_id' )::int) = (org_id, unit_id, user_id);
$$;
CREATE POLICY db_tenant_by ON rls_test AS RESTRICTIVE
USING( rls_guard(org_id, unit_id, user_id );
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
