From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gopisetty, Ramesh <rameshg2@illinois.edu>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Performance issue when we use policies for Row Level Security along with functions
Date: Wed, 16 Sep 2020 10:17:33 -0400
Message-ID: <1502555.1600265853@sss.pgh.pa.us> (raw)
In-Reply-To: <BL0PR11MB3153082AF1A7B16DEE74DCE5EE210@BL0PR11MB3153.namprd11.prod.outlook.com>
References: <BL0PR11MB3153082AF1A7B16DEE74DCE5EE210@BL0PR11MB3153.namprd11.prod.outlook.com>
"Gopisetty, Ramesh" <rameshg2@illinois.edu> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, rameshg2@illinois.edu, pgsql-performance@lists.postgresql.org
Subject: Re: Performance issue when we use policies for Row Level Security along with functions
In-Reply-To: <1502555.1600265853@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox