SECURITY INVOKER模式下get_events函数性能优化咨询

我们有一张名为events的大表，存储大量设备事件数据，每行数据通过RLS（行级安全）检查验证已认证用户是否属于该设备所属组织。我们实现了get_events函数，用于返回指定设备ID数组_device_ids中每个设备的最新_limit条事件，但该函数在SECURITY INVOKER模式下运行极慢，触发了8秒的默认超时。

切换为SECURITY DEFINER模式后，性能大幅提升至约40ms。目前我们采用了一种折中方案：由SECURITY INVOKER模式的函数验证用户权限后，再调用SECURITY DEFINER模式的get_events函数。但我们更希望保持get_events为SECURITY INVOKER模式以避免风险，因此寻求该模式下优化函数性能的方法。

编辑补充：按照Florian Klein的建议为函数添加LEAKPROOF属性后，性能无明显改善。

性能分析语句 #

ALTER FUNCTION is_auth_in_org_of_alias LEAKPROOF;
ALTER FUNCTION is_auth_member_of_org LEAKPROOF;
ALTER FUNCTION private_schema.get_events LEAKPROOF;

SET auto_explain.log_nested_statements = true;
SET auto_explain.log_min_duration=0;
EXPLAIN ANALYZE SELECT e.device_id, e.type, e.timestamp, e.data
            FROM unnest(ARRAY['5a52f07f-c2fb-54b9-b611-21e8fcec678b'::uuid]) AS p(device_id)
            CROSS JOIN LATERAL (
                SELECT e.*
                FROM private_schema.events e
                WHERE e.device_id = p.device_id
                ORDER BY e.timestamp DESC
                LIMIT 20
            ) AS e
            ORDER BY e.device_id, e.timestamp DESC;

执行输出 #

Sort  (cost=3841.50..3841.55 rows=20 width=55) (actual time=7475.117..7475.119 rows=20 loops=1)

  Sort Key: e.device_id, e."timestamp" DESC
  Sort Method: quicksort  Memory: 26kB
  ->  Nested Loop  (cost=3840.61..3841.07 rows=20 width=55) (actual time=7474.995..7475.000 rows=20 loops=1)
        ->  Function Scan on unnest p  (cost=0.00..0.01 rows=1 width=16) (actual time=0.022..0.023 rows=1 loops=1)
        ->  Limit  (cost=3840.60..3840.65 rows=20 width=59) (actual time=7474.968..7474.971 rows=20 loops=1)
              ->  Sort  (cost=3840.60..3845.55 rows=1978 width=59) (actual time=7474.967..7474.968 rows=20 loops=1)
                    Sort Key: e."timestamp" DESC
                    Sort Method: top-N heapsort  Memory: 47kB
                    ->  Bitmap Heap Scan on events e  (cost=89.29..3787.97 rows=1978 width=59) (actual time=9.051..7438.728 rows=44280 loops=1)
                          Recheck Cond: (device_id = p.device_id)
                          Filter: is_auth_in_org_of_alias(device_id)
                          Heap Blocks: exact=2036
                          ->  Bitmap Index Scan on events_device_id  (cost=0.00..88.80 rows=5934 width=0) (actual time=5.329..5.329 rows=44280 loops=1)
                                Index Cond: (device_id = p.device_id)
Planning Time: 1.101 ms
Execution Time: 7475.282 ms

RLS策略代码 #

CREATE FUNCTION is_auth_member_of_org(_organization_id uuid)
  RETURNS boolean
  LANGUAGE sql
  SECURITY DEFINER
  AS $function$
    SELECT EXISTS (
      SELECT 1
      FROM public.roles
      WHERE user_id = auth.uid()
      AND organization_id = _organization_id
    )
  $function$;

CREATE OR REPLACE FUNCTION is_auth_in_org_of_alias(_device_id UUID)
  RETURNS BOOLEAN
  SECURITY DEFINER
  LANGUAGE plpgsql
  AS $$
BEGIN
  RETURN is_auth_member_of_org(
    (SELECT organization_id FROM public.devices WHERE alias = _device_id)
  );
END;
$$;

CREATE POLICY "Authenticated have full access to events table of devices in the orgs they are members in"
  ON toit_artemis.events
  FOR ALL
  TO authenticated
  USING (public.is_auth_in_org_of_alias(device_id))
  WITH CHECK (public.is_auth_in_org_of_alias(device_id));

内容的提问来源于stack exchange，提问作者Florian Loitsch