Row level security(RLS) performance is significantly slower in postgres.
<p>Description :
Here is the sample demonstration of the performance issue.</p>

<p>We first created two tables  , enabled row level security and created policy as well .</p>

<p>Table definition:</p>

<pre><code>create table sample_schema.sample_table1(ID numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 Description VARCHAR(255)
        );

create table sample_schema.sample_table2(ID2 numeric(38) PRIMARY KEY NOT NULL,
                 tenant_id VARCHAR(255) NOT NULL,
                 table1_id numeric (38),
                 Description2 VARCHAR(255)
        );    
</code></pre>

<p>Index creation:</p>

<pre><code>CREATE UNIQUE INDEX sample_table1_idx1 ON sample_schema.sample_table1(tenant_id,id);            
</code></pre>

<p>Enable row level security:    </p>

<pre><code>ALTER TABLE sample_schema.sample_table1 ENABLE ROW LEVEL SECURITY;   
</code></pre>

<p>create role:</p>

<pre><code>CREATE ROLE tenant_grp_role_p_id;    
</code></pre>

<p>Create policy:I want a policy to select data where tenant_id column value has a  role that is same as the user who has logged in.</p>

<pre><code>CREATE POLICY Tenant_Roles ON  sample_schema.sample_table1 TO tenant_grp_role_p_id USING ((tenant_id) IN ( SELECT rolname FROM pg_roles WHERE    pg_has_role( current_user, oid, 'member')));
</code></pre>

<p>create sample data:</p>

<pre><code>insert into sample_schema.sample_table1 values (1,'user1_tenant1',1,'Table1 Data');
insert into sample_schema.sample_table2 values (2,'user1_tenant1',1,'Table2 Data');
</code></pre>

<p>Problem:below query is not using primary_key index .</p>

<pre><code>SELECT * FROM sample_schema.sample_table1 ST1,  sample_schema.sample_table2 T2 WHERE ST1.id = ST2.table1_id  AND ST1.id = 1;    
</code></pre>

<p>Question:If I disable RLS then primary key index is used .why is it not using primary key index scan when RLS is enabled?</p>

<p>Note :<br>
A.If I disable the row level security and  run the above query  ,it uses the index.<br>
B.below is the explain plan Output when low level security is disabled .</p>

<pre><code>Nested Loop  (cost=0.29..19.19 rows=1 width=1129)  -&gt;  Index Scan using sample_table1_pkey on sample_table1 st1  (cost=0.29..8.30 rows=1 width=37)
    Index Cond: (id = '1'::numeric)  -&gt;  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)        Filter: (table1_id = '1'::numeric);    
</code></pre>

<p>C.if I enable the low level security and run the query it does not use index.<br>
and below is the explain plan Output when low level security is enabled.    </p>

<pre><code> Nested Loop  (cost=1.03..946.65 rows=79 width=1129) -&gt;  Seq Scan on sample_table2 st2  (cost=0.00..10.88 rows=1 width=1092)  Filter: (table1_id = '1'::numeric)  -&gt;  Subquery Scan on st1  (cost=1.03..934.98 rows=79 width=37)
    Filter: (st1.id = '1'::numeric)        -&gt;  Hash Join  (cost=1.03..738.11 rows=15750 width=37)              Hash Cond: ((st1_1.tenant_id)::name = pg_authid.rolname)              -&gt;  Seq Scan on sample_table1 st1_1  (cost=0.00..578.00 rows=31500 width=37)              -&gt;  Hash  (cost=1.01..1.01 rows=1 width=68)                    -&gt;  Seq Scan on pg_authid  (cost=0.00..1.01 rows=1 width=68)                          Filter: pg_has_role("current_user"(), oid, 'member'::text);   
</code></pre>

<p>Please help me resolve this issue ..</p>