Postgresql Multi-tenant Mode not using Index

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)    );

CREATE UNIQUE INDEX sample_table1_idx1 ON        
      sample_schema.sample_table1(tenant_id,id);        

CREATE ROLE tenant_grp_role_p_id
         NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

create user user1_tenant1;
GRANT tenant_grp_role_p_id to user1_tenant1;
GRANT all on schema sample_schema to tenant_grp_role_p_id;
GRANT select,update,insert,delete on sample_schema.sample_table1  
        TO tenant_grp_role_p_id;


ALTER TABLE sample_schema.sample_table1 ENABLE ROW LEVEL SECURITY;
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'))    )      ;

   insert into sample_schema.sample_table1 
     values (1,'user1_tenant1','My Data One');
  insert...
  insert...

  -- Inserted around 30000 Rows

  insert into sample_schema.sample_table2 values       
  (1,'user1_tenant1',1,'Table2 Data1');

  -- Inserted around 20 Rows

  select * from sample_schema.sample_table1 

  set role to user1_tenant1;

  SELECT * 
  FROM sample_schema.sample_table1 ST1, 
    sample_schema.sample_table2 ST2
  WHERE ST1.id = ST2.table1_id
    AND ST1.id = 1;

Explain Plan Output Without Policy

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

With Multi-tenant Policy

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

Without Multi-tenant Policy enabled, Postgresql uses Index, where as after enabling the Policy it does not use the Index either the PKey or any Other Index Which I have created

What are the settings required in Postgresql to use the Indexes what we have created when Row Level Security is enabled.

Recommended topics

Hot tags