行级安全,性能差

我正在评估使用PostgreSQL的行级安全(RLS)功能来软删除客户的可能性。不幸的是,我的表现不佳。以下是PostgreSQL 9.5.10版中的一个简单测试设置:

包含10000000个客户的表:

CREATE TABLE customers (
    customer_id integer PRIMARY KEY,
    name text,
    hidden boolean DEFAULT FALSE
);

INSERT INTO customers (customer_id, name) SELECT generate_series(0, 9999999), 'John Doe';
ANALYZE customers;

包含每个客户一个订单的表:

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    customer_id integer REFERENCES customers (customer_id)
);

INSERT INTO orders (order_id, customer_id) SELECT generate_series(0, 9999999), generate_series(0, 9999999);
ANALYZE orders;

将执行此操作的不受信任用户仅选择:

CREATE ROLE untrusted;
GRANT SELECT ON customers TO untrusted;
GRANT SELECT ON orders TO untrusted;

使隐藏客户对未受信任的用户不可见的策略:

CREATE POLICY no_hidden_customers ON customers FOR SELECT TO untrusted USING (hidden IS FALSE);
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

一个简单的测试查询:order\u id=4711下订单的客户的名字是什么?

无RLS:

EXPLAIN ANALYZE SELECT name FROM orders JOIN customers USING (customer_id) WHERE order_id = 4711;
                                                           QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.87..16.92 rows=1 width=9) (actual time=0.121..0.123 rows=1 loops=1)
   ->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=4) (actual time=0.078..0.078 rows=1 loops=1)
         Index Cond: (order_id = 4711)
   ->  Index Scan using customers_pkey on customers  (cost=0.43..8.45 rows=1 width=13) (actual time=0.039..0.040 rows=1 loops=1)
         Index Cond: (customer_id = orders.customer_id)
 Planning time: 0.476 ms
 Execution time: 0.153 ms
(7 rows)

使用RLS:

EXPLAIN ANALYZE SELECT name FROM orders JOIN customers USING (customer_id) WHERE order_id = 4711;
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=8.46..291563.48 rows=1 width=9) (actual time=1.494..2565.121 rows=1 loops=1)
   Hash Cond: (customers.customer_id = orders.customer_id)
   ->  Seq Scan on customers  (cost=0.00..154055.00 rows=10000000 width=13) (actual time=0.010..1784.086 rows=10000000 loops=1)
         Filter: (hidden IS FALSE)
   ->  Hash  (cost=8.45..8.45 rows=1 width=4) (actual time=0.015..0.015 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Index Scan using orders_pkey on orders  (cost=0.43..8.45 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)
               Index Cond: (order_id = 4711)
 Planning time: 0.358 ms
 Execution time: 2565.170 ms
(10 rows)

加入表格时,如何避免顺序扫描?我已经尝试了我能想到的所有索引,但都没有用。