Row level security performance joining large tables

From: "David R(dot) Pike" <david(dot)pike(at)trustedconcepts(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Row level security performance joining large tables
Date: 2016-10-28 17:15:57
Message-ID: 103877619.228905.1477674957518.JavaMail.root@trustedconcepts.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries against a single large RLS protected table perform well however queries that join several large RLS protected tables perform very poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before executing the primary key join that would reduce the query results to a single row from each table. Clearly performance would be better if it performed the join before the policy check.

From what I can understand the RLS implementation strives to execute policy checks before user provided predicate checks so as to avoid leaking protected data. Is there any way to make the join look "safe" to the optimizer to avoid full table scans? Isn't this a common scenario?

Thanks,
Dave

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2016-10-28 17:36:45 Re: How to hint 2 coulms IS NOT DISTINCT FROM each other
Previous Message Jehan-Guillaume de Rorthais 2016-10-28 16:49:59 Re: Fast Primary shutdown only after wal_sender_timeout