| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | "David R(dot) Pike" <david(dot)pike(at)trustedconcepts(dot)com> |
| Cc: | pgsql-general(at)postgresql(dot)org |
| Subject: | Re: Row level security performance joining large tables |
| Date: | 2016-10-29 21:33:49 |
| Message-ID: | 6336.1477776829@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
"David R. Pike" <david(dot)pike(at)trustedconcepts(dot)com> writes:
> 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.
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Samuel Williams | 2016-10-29 22:02:57 | Re: initdb createuser commands |
| Previous Message | Steven Hirsch | 2016-10-29 21:25:23 | Re: What is the 'data2' directory for? |