我注意到隐式指定WHERE tenant_id = ?子句大大提高了USING (tenant_id = get_tenant_id())的性能，我真的不明白为什么。
我有两个房客一号和二号。
租户1有大约100000行。
租户2有大约6000行。

SET app.tenant_id = 2; SET ROLE user;
EXPLAIN ANALYZE SELECT count(*) FROM people;
RESET ROLE; RESET app.tenant_id;

Aggregate  (cost=32570.08..32570.09 rows=1 width=8) (actual time=62.855..62.856 rows=1 loops=1)
  ->  Index Only Scan using index_people_on_tenant_id on people  (cost=0.29..32429.13 rows=56381 width=0) (actual time=58.639..62.642 rows=6000 loops=1)
        Filter: (tenant_id = get_tenant_id())
        Rows Removed by Filter: 101125
        Heap Fetches: 57059
Planning Time: 1.979 ms
Execution Time: 65.704 ms

正如您所看到的，正在应用过滤器，它正确地删除了101125行。但是执行时间超级慢。
现在，当执行带有隐式where子句的语句时。

SET app.tenant_id = 2; SET ROLE user;
EXPLAIN ANALYZE SELECT count(*) FROM people where tenant_id = 2;
RESET ROLE; RESET app.tenant_id;

Aggregate  (cost=1818.55..1818.56 rows=1 width=8) (actual time=13.079..13.080 rows=1 loops=1)
  ->  Index Only Scan using index_people_on_tenant_id on people  (cost=0.29..1810.77 rows=3112 width=0) (actual time=0.467..12.348 rows=6000 loops=1)
        Index Cond: (tenant_id = 2)
        Filter: (tenant_id = get_tenant_id())
        Heap Fetches: 11826
Planning Time: 1.867 ms
Execution Time: 15.641 ms

为什么filter不使用索引进行过滤？
政策定义

ALTER TABLE people_policy ENABLE ROW LEVEL SECURITY;

CREATE POLICY people_policy ON people FOR ALL TO user USING (tenant_id = get_tenant_id());

CREATE OR REPLACE FUNCTION get_tenant_id() RETURNS BIGINT AS $$
BEGIN
  RETURN NULLIF(current_setting('app.tenant_id', TRUE), '')::BIGINT;
END;
$$ LANGUAGE plpgsql;