Using row-level security results in less optimal query plans
От | Charles |
---|---|
Тема | Using row-level security results in less optimal query plans |
Дата | |
Msg-id | CADBa3wZpuYXwdry2g68NxYmAXOPvx0DLvdAU4kdo2GoPtxKu+Q@mail.gmail.com обсуждение исходный текст |
Список | 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
В списке pgsql-bugs по дате отправления: