Обсуждение: Row level security performance joining large tables
I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries againsta single large RLS protected table perform well however queries that join several large RLS protected tables performvery poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before executingthe primary key join that would reduce the query results to a single row from each table. Clearly performance wouldbe 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 checksso as to avoid leaking protected data. Is there any way to make the join look "safe" to the optimizer to avoid fulltable scans? Isn't this a common scenario? Thanks, Dave
"David R. Pike" <david.pike@trustedconcepts.com> writes: > I recently applied RLS to several large (several million rows) tables in my 9.5 database and noticed that queries againsta single large RLS protected table perform well however queries that join several large RLS protected tables performvery poorly. The explain plan shows the optimizer is scanning the entire table to enforce the RLS policy before executingthe primary key join that would reduce the query results to a single row from each table. Clearly performance wouldbe better if it performed the join before the policy check. Join cases with RLS aren't optimized very well at the moment. There's work afoot to improve this - see https://www.postgresql.org/message-id/flat/8185.1477432701%40sss.pgh.pa.us - but it won't be in production before v10. regards, tom lane
David, * David R. Pike (david.pike@trustedconcepts.com) wrote: > From what I can understand the RLS implementation strives to execute policy checks before user provided predicate checksso as to avoid leaking protected data. Is there any way to make the join look "safe" to the optimizer to avoid fulltable scans? Isn't this a common scenario? You can use a security barrier view which is owned by the same user that the tables underneath are owned by, that will bypass RLS on the tables themselves and therefore you'll need to implement the appropriate quals in the security barrier view. As Tom mentions, we're working to improve RLS optimization as well. As is pretty common with various features, the initial implementation provides the functionality but perhaps isn't as performant as one might like, and then we iterate and improve it in the subsequent releases. Thanks! Stephen