Re: Single column vs composite partial index
> You're asking whether to keep one index or the other?
My ask is which index can be used for the mentioned query in production for
better IO
> It depends on *all* the queries you'll run, not just this one.
I'm more concerned about this specific query, this has been using in one block
stored procedure, so it will be run more often on the table.
explain(ANALYZE, BUFFERS) output:
"Subquery Scan on s (cost=32023.15..33123.52 rows=129 width=61) (actual
time=2.615..2.615 rows=0 loops=1)"" Filter: (s.rnk = 1)"" Buffers: shared
hit=218"" -> WindowAgg (cost=32023.15..32799.88 rows=25891 width=61) (actual
time=2.614..2.615 rows=0 loops=1)"" Buffers: shared hit=218"" ->
Sort (cost=32023.15..32087.88 rows=25891 width=53) (actual time=2.613..2.613
rows=0 loops=1)"" Sort Key: ct.ban, ct.subscriber_no,
ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.load_dttm DESC""
Sort Method: quicksort Memory: 25kB"" Buffers: shared
hit=218"" -> Bitmap Heap Scan on l_csm_transactions ct
(cost=1449.32..30125.32 rows=25891 width=53) (actual time=2.605..2.605 rows=0
loops=1)"" Recheck Cond: (((load_dttm)::date >=
(CURRENT_DATE - 7)) AND ((actv_code)::text = ANY
('{NAC,CAN,RSP,RCL}'::text[])))"" Buffers: shared hit=218""
-> Bitmap Index Scan on
l_csm_transactions_actv_code_load_dttm_idx1 (cost=0.00..1442.85 rows=25891
width=0) (actual time=2.602..2.602 rows=0 loops=1)""
Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))""
Buffers: shared hit=218""Planning Time: 0.374 ms""Execution Time: 2.661 ms"
>The actual performance might change based on thing like maintenance like
>reindex, cluster, vacuum, hardware, and DB state (like cached blocks).
Note: Stats are up to date
> And Postgres version.
PostgreSQL 11.7 running on RedHat
Thanks,Rj
On Tuesday, September 15, 2020, 09:18:55 PM PDT, Justin Pryzby
wrote:
On Tue, Sep 15, 2020 at 10:33:24PM +, Nagaraj Raj wrote:
> Hi,
> I'm running one query, and I created two types of index one is composite and
> the other one with single column one and query planner showing almost the
> same cost for both index bitmap scan, I'm not sure which is appropriate to
> keep in production tables.
You're asking whether to keep one index or the other ?
It depends on *all* the queries you'll run, not just this one.
The most general thing to do would be to make multiple, single column indexes,
and let the planner figure out which is best (it might bitmap-AND or -OR them
together).
However, for this query, you can see the 2nd query is actually faster (2ms vs
56ms) - the cost is an estimate based on a model.
The actual performance might change based on thing like maintenance like
reindex, cluster, vacuum, hardware, and DB state (like cached blocks).
And postgres version.
The rowcount estimates are bad. Maybe you need to ANALYZE the table (or adjust
the autoanalyze thresholds), or evaluate if there's a correlation between
columns. Bad rowcount estimates beget bad plans and poor performance.
Also: you could use explain(ANALYZE,BUFFERS).
I think the fast plan would be possible with a tiny BRIN index on load_dttm.
(Possibly combined indexes on actv_code or others).
If you also have a btree index on time, then you can CLUSTER on it (and
analyze) and it might improve that plan further (but would affect other
queries, too).
> explain analyze SELECT BAN, SUBSCRIBER_NO, ACTV_CODE, ACTV_RSN_CODE,
> EFFECTIVE_DATE, TRX_SEQ_NO, LOAD_DTTM, rnk AS RNK FROM ( SELECT CT.BAN,
> CT.SUBSCRIBER_NO, CT.ACTV_CODE, CT.ACTV_RSN_CODE, CT.EFFECTIVE_DATE,
> CT.TRX_SEQ_NO, CT.LOAD_DTTM, row_number() over (partition by CT.BAN,
> CT.SUBSCRIBER_NO, CT.ACTV_CODE, CT.ACTV_RSN_CODE order by CT.TRX_SEQ_NO DESC,
> CT.LOAD_DTTM DESC) rnk FROM SAM_T.L_CSM_TRANSACTIONS CT WHERE CT.ACTV_CODE in
> ( 'NAC', 'CAN', 'RSP', 'RCL') AND LOAD_DTTM::DATE >= CURRENT_DATE - 7 ) S
> WHERE RNK = 1
> 1st Index with single column:
> CREATE INDEX l_csm_transactions_load_dttm_idx1 ON sam_t.l_csm_transactions
> USING btree (load_dttm ASC NULLS LAST)
> /*"Subquery Scan on s (cost=32454.79..33555.15 rows=129 width=61) (actual
>time=56.473..56.473 rows=0 loops=1)
> Filter: (s.rnk = 1)
> -> WindowAgg (cost=32454.79..33231.52 rows=25891 width=61) (actual
>time=56.472..56.472 rows=0 loops=1)
> -> Sort (cost=32454.79..32519.51 rows=25891 width=53) (actual
>time=56.470..56.470 rows=0 loops=1)
> Sort Key: ct.ban, ct.subscriber_no, ct.actv_code,
>ct.actv_rsn_code, ct.trx_seq_no DESC, ct.load_dttm DESC
> Sort Method: quicksort Memory: 25kB
> -> Bitmap Heap Scan on l_csm_transactions ct
>(cost=1271.13..30556.96 rows=25891 width=53) (actual time=56.462..56.462
>rows=0 loops=1)
> Recheck Cond: ((actv_code)::text = ANY
>('{NAC,CAN,RSP
Performance issue when we use policies for Row Level Security along with functions
Hi,
I'm seeing a strange behavior when we implement policies (for RLS - Row level
security) using functions.
table test consists of columns testkey,oid,category,type,description...
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.
pgwfc01q=> explain analyze select * from test;
QUERY PLAN
Seq Scan on test (cost=0.00..25713.12 rows=445 width=712) (actual
time=1849.592..1849.592 rows=0 loops=1)
Filter: ((testkey )::text = (f_sel_policy_test(testkey ))::text)
Rows Removed by Filter: 88930
Planning Time: 0.414 ms
Execution Time: 1849.614 ms
(5 rows)
The function is
CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character
varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
Declare
v_status character varying;
BEGIN
if vpd_key = 'COMMON' then
return '''COMMON''';
elsif vpd_key = ('COMMON_' || SYS_CONTEXT('ctx_ng',
'ctx_prod_locale')) then
return '''COMMON_' || SYS_CONTEXT('ctx_ng',
'ctx_prod_locale')||;
elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then
return '''co'','''||SYS_CONTEXT('ctx_ng',
'ctx_testkey_fil')||;
end if;
return 'false';
exception when undefined_object then
return 'failed';
END;
$function$
;
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')));
QUERY PLAN
-
Bitmap Heap Scan on test (cost=396.66..2966.60 rows=13396 width=712) (actual
time=0.693..2.318 rows=13159 loops=1)
Recheck Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character varying,
(current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[]))
Heap Blocks: exact=373
-> Bitmap Index Scan on test_pkey (cost=0.00..393.31 rows=13396 width=0)
(actual time=0.653..0.653 rows=13159 l
oops=1)
Index Cond: ((testkey )::text = ANY ((ARRAY['COMMON'::character
varying, (current_setting('ctx_vpd.ctx
_key_fil'::text))::character varying])::text[]))
Planning Time: 0.136 ms
Execution Time: 2.843 ms
(7 rows)
If i replace the policy with stright forward without function then it chooses
the index. Not sure how i can implement with the function. I thought of
creating the policy with a lot of business logic in the function. If i have
the function then i notice going for full table scan instead of index.
Please help me if i miss anything in writing a function or how to use functions
in the policy.
Thank you.
Regards,
Ramesh G
Re: Performance issue when we use policies for Row Level Security along with functions
De: "Gopisetty, Ramesh"
Para: "pgsql-performance"
Enviadas: Quarta-feira, 16 de setembro de 2020 0:39:08
Assunto: Performance issue when we use policies for Row Level Security along
with functions
BQ_BEGIN
Hi,
I'm seeing a strange behavior when we implement policies (for RLS - Row level
security) using functions.
table test consists of columns testkey,oid,category,type,description...
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.
pgwfc01q=> explain analyze select * from test;
QUERY PLAN
Seq Scan on test (cost=0.00..25713.12 rows=445 width=712) (actual
time=1849.592..1849.592 rows=0 loops=1)
Filter: (( testkey )::text = (f_sel_policy_test( testkey ))::text)
Rows Removed by Filter: 88930
Planning Time: 0.414 ms
Execution Time: 1849.614 ms
(5 rows)
The function is
CREATE OR REPLACE FUNCTION vpd_sec_usr.f_sel_policy_test(testkey character
varying)
RETURNS character varying
LANGUAGE plpgsql
AS $function$
Declare
v_status character varying;
BEGIN
if vpd_key = 'COMMON' then
return ''' COMMON ''';
elsif vpd_key = (' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')) then
return ''' COMMON_ ' || SYS_CONTEXT('ctx_ng', 'ctx_prod_locale')||;
elsif vpd_key = SYS_CONTEXT('ctx_ng_vpd', 'ctx_key_fil') then
return '''co'','''||SYS_CONTEXT('ctx_ng', 'ctx_testkey_fil')||;
end if;
return 'false';
exception when undefined_object then
return 'failed';
END;
$function$
;
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')));
QUERY PLAN
-
Bitmap Heap Scan on test (cost=396.66..2966.60 rows=13396 width=712) (actual
time=0.693..2.318 rows=13159 loops=1)
Recheck Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying,
(current_setting('ctx_vpd.ctx_key_fil'::text))::character varying])::text[]))
Heap Blocks: exact=373
-> Bitmap Index Scan on test_pkey (cost=0.00..393.31 rows=13396 width=0)
(actual time=0.653..0.653 rows=13159 l
oops=1)
Index Cond: (( testkey )::text = ANY ((ARRAY['COMMON'::character varying,
(current_setting('ctx_vpd.ctx
_key_fil'::text))::character varying])::text[]))
Planning Time: 0.136 ms
Execution Time: 2.843 ms
(7 rows)
If i replace the policy with stright forward without function then it chooses
the index. Not sure how i can implement with the function. I thought of
creating the policy with a lot of business logic in the function. If i have the
function then i notice going for full table scan instead of index.
Please help me if i miss anything in writing a function or how to use functions
in the policy.
Thank you.
Regards,
Ramesh G
BQ_END
You could try seeting the function as immutable. By default it is volatile.
Re: Performance issue when we use policies for Row Level Security along with functions
"Gopisetty, Ramesh" 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
