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 по дате отправления:

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: BUG #13681: Serialization failures caused by new multixact code of 9.3 (back-patch request)
Следующее
От: Peter Geoghegan
Дата:
Сообщение: Re: BUG #14210: filter by "=" constraint doesn't work when hash index is present on a column