Using row-level security results in less optimal query plans

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

Browse pgsql-bugs by date

  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)