Row security policy & partitions in PostgreSQL

Question

A PostgreSQL 10.6 table has row security policy enabled on a field, say id. The table has multiple partitions based on id.

Before executing a select query, the value for the field was set using the SET statement.

But when the select query is run, I see from the explain plan that PostgreSQL has to scan through all partitions to find the right partition corresponding to the value that was set using the SET statement.

Whereas if the id was passed in the WHERE clause, it picked the right partition without scanning through them all.

Is there a way to make a row security policy pick the correct partition similar to the where clause?

Answer 1

If your policy depends on a parameter being set, you probably use current_setting() to retrieve it.

Since the value is not known when the query is parsed, PostgreSQL v10 cannot apply partition pruning.

Depending on your query, upgrading to v11 may help, because this version has added support for partition pruning at query execution time.