【PostgreSQL】Row Level Securityを有効化したテーブルでSQLの実行計画を検証する2
Row Level Securityに使用する列にインデックスを張ってみる
この記事は【PostgreSQL】Row Level Securityを有効化したテーブルでSQLの実行計画を検証する
の続きです。今回は前回使用したテーブルと同じ列を持つテーブルに対してRow Level Securityに使用する列に対してインデックスを張ります。また、テナント数を10に増やし、各テナントに
20,000件ずつデータを投入し実行計画を検証しました。
検証用のテーブルとデータの準備
以下のSQLを実行して検証用のテーブル作成とデータ投入を行いました。
create table customer2 (
id int primary key, -- 主キー
tenant_name text, -- マルチテナントを想定したテナント名
customer_name text, -- 顧客名
customer_div int, -- 顧客区分
birthday timestamp with time zone -- 顧客の生年月日
);
insert into customer2 (
id,
tenant_name,
customer_name,
customer_div,
birthday
)
select
i,
case -- 10個のテナント名を生成
when mod(i, 10) = 0 then 'tenant1'
when mod(i, 10) = 1 then 'tenant2'
when mod(i, 10) = 2 then 'tenant3'
when mod(i, 10) = 3 then 'tenant4'
when mod(i, 10) = 4 then 'tenant5'
when mod(i, 10) = 5 then 'tenant6'
when mod(i, 10) = 6 then 'tenant7'
when mod(i, 10) = 7 then 'tenant8'
when mod(i, 10) = 8 then 'tenant9'
when mod(i, 10) = 9 then 'tenant10'
end,
left(md5(i::text), 10),
mod(i, 5), -- 顧客区分値として0~4 を生成
timestamp '1970-01-01 00:00:00' + random() * (timestamp '2000-12-31 23:59:59' - timestamp '1970-01-01 00:00:00')
from
generate_series(1, 200000, 1) as i;
-- Row Level Security有効化とポリシー設定
alter table customer2 enable row level security;
create policy tenant_policy on customer2 for all using (tenant_name = current_user);
-- tenant_nameとbirthdayにインデックスを張る
create index index_customer2_tenant_name on customer2 (tenant_name);
create index index_customer2_birthday on customer2 (birthday);
-- テーブルへのアクセス権限を付与
grant all on all tables in schema public to tenant1;
grant all on all tables in schema public to tenant2;
実行計画の検証
以下のSQLの実行計画を検証しました。where句にRow Level Securityで使用するtenant_nameを指定しない場合でもtenant_nameに作成したindex
が使用されていることがわかります。また、birthdayをwhere句の条件に入れた場合は2つのインデックスが使用されています。
これらのことから、Row Level Securityで使用する列にもインデックスを張った方がよいと思います。しかし、テナントごとのデータ数に偏りがある場合にインデックスは
使用されません。tenant1のみデータを50万件追加したのち select * from customer2 を実行するとtenant1ロールではインデックスが使用されませんでした。
これは通常の列に対するインデックスと同じです。
set role to tenant1;
explain analyze select * from customer2;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customer2 (cost=395.66..2422.24 rows=20547 width=35) (actual time=15.796..212.516 rows=20000 loops=1)
Recheck Cond: (tenant_name = (CURRENT_USER)::text)
Heap Blocks: exact=1667
-> Bitmap Index Scan on index_customer2_tenant_name (cost=0.00..390.53 rows=20547 width=0) (actual time=15.564..15.573 rows=20000 loops=1)
Index Cond: (tenant_name = (CURRENT_USER)::text)
Planning Time: 40.754 ms
Execution Time: 398.812 ms
(7 rows)
explain analyze select * from customer2 where '1990-01-01' <= birthday and birthday <= '1990-12-31';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on customer2 (cost=527.58..1794.62 rows=658 width=35) (actual time=16.821..27.297 rows=665 loops=1)
Recheck Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday <= '1990-12-31 00:00:00+00'::timestamp with time zone) AND (tenant_name = (CURRENT_USER)::text))
Heap Blocks: exact=549
-> BitmapAnd (cost=527.58..527.58 rows=658 width=0) (actual time=16.746..16.753 rows=0 loops=1)
-> Bitmap Index Scan on index_customer2_birthday (cost=0.00..136.47 rows=6405 width=0) (actual time=15.435..15.442 rows=6306 loops=1)
Index Cond: (('1990-01-01 00:00:00+00'::timestamp with time zone <= birthday) AND (birthday <= '1990-12-31 00:00:00+00'::timestamp with time zone))
-> Bitmap Index Scan on index_customer2_tenant_name (cost=0.00..390.53 rows=20547 width=0) (actual time=1.202..1.209 rows=20000 loops=1)
Index Cond: (tenant_name = (CURRENT_USER)::text)
Planning Time: 6.923 ms
Execution Time: 35.811 ms
(10 rows)