From: | Charles <cooper(dot)charles(dot)m(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Using row-level security results in less optimal query plans |
Date: | 2016-07-15 18:48:21 |
Message-ID: | CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Basic summary of the problem:
Using a row-level security filter on one column in a 12GB table results in
a slow bitmap heap scan instead of the fast index scan used by the query
executed as the owner of the table (without any row-level security).
Writing in the filter condition by hand also results in a fast index scan.
The table description can be found here:
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-table_description
The original query can be found here:
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-original_query-sql
The query with the filter spliced in by hand can be found here:
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-filtered_query-sql
And the query which uses the row-based mechanism here:
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-row-based_query-sql
The query plans are below:
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-original_query-plan
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-filtered_query-plan
https://gist.github.com/charles-cooper/abccf41ce5a1175635414321622fba57#file-row-based_query-plan
I have seen similar-looking plans break here (
https://www.postgresql.org/message-id/fad91c29-5250-f856-2347-61122d4901ab%40gmail.com
, http://pastebin.com/zymkbcSf) and here (
https://www.postgresql.org/message-id/CADBa3wbkFAw5EVYskKU-aJv1K2CN72FPQVDrLvszGHdWErGJ4w@mail.gmail.com
)
pgsql version: 9.5.3
OS: Ubuntu Xenial 16.04, kernel 3.14.32
Best,
Charles
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2016-07-16 02:21:06 | Re: BUG #14210: filter by "=" constraint doesn't work when hash index is present on a column |
Previous Message | Alvaro Herrera | 2016-07-15 18:23:13 | Re: BUG #13681: Serialization failures caused by new multixact code of 9.3 (back-patch request) |