Re: Performance issue when we use policies for Row Level Security along with functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Gopisetty, Ramesh" <rameshg2@xxxxxxxxxxxx> writes:
> Policy
> create policy  policy_sel on test FOR SELECT to ram1 USING  (  testkey in (f_sel_policy_test(testkey))  );
> Going to a Sequential scan instead of index scan.  Hence, performance issue.

> If i replace the policy with stright forward without function then it chooses the index.   Not sure how i can implement with the function.
> create policy  policy_sel on test FOR SELECT to ram1 USING  ( testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')));

" testkey in ('COMMON',current_setting('ctx_ng'||'.'||'ctx_key_fil')) "
is an indexable condition on testkey, because it compares testkey to
a constant (or at least, a value that's fixed for the life of the query).

" testkey in (f_sel_policy_test(testkey)) "
is not an indexable condition on anything, because there are variables
on both sides of the condition.  So there's no fixed value that the
index can search on.

If you intend f_sel_policy_test() to be equivalent to the other condition,
why are you passing it an argument it doesn't need?

As Luis noted, there's also the problem that an indexable condition
can't be volatile.  I gather that SYS_CONTEXT ends up being a probe
of some GUC setting, which means that marking the function IMMUTABLE
would be a lie, but you ought to be able to mark it STABLE.

			regards, tom lane






[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux