Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kIJW7-0003jA-WC for pgsql-performance@arkaria.postgresql.org; Tue, 15 Sep 2020 22:33:40 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kIJW5-0002Ih-1U for pgsql-performance@arkaria.postgresql.org; Tue, 15 Sep 2020 22:33:37 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kIJW3-0002HB-Rs for pgsql-performance@lists.postgresql.org; Tue, 15 Sep 2020 22:33:36 +0000 Received: from sonic315-21.consmr.mail.ne1.yahoo.com ([66.163.190.147]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kIJVz-0006RI-LY for pgsql-performance@lists.postgresql.org; Tue, 15 Sep 2020 22:33:33 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1600209208; bh=FlkhiYWOAChZ9Q4WTD6EDVi5vEE5Tl3Viu3jt28PpGQ=; h=Date:From:To:Subject:References:From:Subject; b=lv69uu9Bjj6U8KP+Q9ACSg8bnrKquTa8uUnxOTdYjcNSOY7DIXO7RLTXu7PY+SNSVjr1+FkJxbb8142nyOOaroFVON9/qnBvPuM/aVEVWp/19p5h8nxWGL3rqmDp2QuneX82R2JIRYM3zIzbsKPqpEJ1nRxLwZjgTB+869Z4dVMfuKe0SsiKny2qEvA0MJTV3ORXytp/qasBKbpSd6Kn8pIuAccjS+hT/FfII76rdFFnl1+5FaafDf/KULGBzTEAI7rkqFh1opyUZQZijC+OYc77g1myru6rE6oqI3Ycp7vStu1z1I97ftGqo2QqXJlk9TixlN4NdZtWHoA7vr020Q== X-YMail-OSG: gTT7OskVM1mONR7dMptxAkCEGVvEBEUq7FNPATbIEja_zTQyoJTWx6rEwdxoXfd ._xOG86Qx.OxMz5FsiMP8JCZ1Xc.quusZ2ZYA7WSxzaqmhaE2FXWmWVBTFvsZk97jVBHySybtMTW vWPRXTX7tC6tdaYm2.3ar3uxPGoKHP0wNOuN1vOKOAaqRaV8IAFzddSYm4ve0N5Hn._cnZl1BJMn oHJG9iR.SEyP49SL7iCVqOjUYr3LUZccuT9fnM1NNHjil6E.XcyZ8vPX4FxVQ8VCvVKZTE1Ybqle v7exauu5x93_XtWcODnVqdmTLuvlNt4n8W7GaNnc9J4KBD9vpQqtvVmHGwH8DRWLqL6fvoeZtoRy Jb7_Hm_.Pkh4_aKEjVK_FLLNzWu21iulnAsTt9TT1C4RLDiq9pU0.cym0HuuFlHDqRVGgDtR7Gkd n29Qzi4ls.A4mR5O2ra9zDA_3u3ZxLxeOMLRG5VnvfxpyzjDLxEbFvh4yf6d2IcT2s1t.Lr6Uzkx oRl9IqQdGyvoJRh5l_fDt3SIJ72HgHhtkgLy9XPILnBF7QjphGrfFwLMc2eTVip2ZLM0n70BgIvp e4swGJBXBZjKPGoF.UwDp1VCN_H95ayK0iBxMBJHB2Q8nwVthmdvzxZGtSrdsBEBT3L8Foc4z3ms RXZdn3ZKX4epzb.K9TqwdIKUB_nc.NAVI3efHEDB04.J6BuzMb.gvbefO4aRoN52mcLlqOtQ6J1V Xec2I0Xrgp4ChbBv35BfPsFrR.tHfR5J9JdPv51Ax99dNHpVlgZRsKKxkEYTLSDxqNo2eXKJ23_3 FEA0bW.Rzb7g8qiOUdn_Srkva3joetpw90NRVy.Ge9wvqoaxRvsEK0hzNNV8qgbMV4RoEiI87Zwl h4vd56NFnkAmnZDYK3_cWm7evKGng5GqwL1eWcqaUIlUH3tnkjJoXVLGyUQKOztR6LKDN_gEfnMq PDOSreFATAE6AFVY0sGInC_AoPZ4kWLZ38SMEDIG6b7CkzbSBc48k9GIYsjDLHNs_d62dJLVCcJW eXnKzw9_azgmXLUAVQhhFQ8UxT6nzUQvXGajCbGgNhVHAkyaOG73NE1RVRKMnt.E178fiJMLaz7x H92c.ZWbUr_YYS4eLKdzSP7as1RFuIYCO0XwkKwawpP_rhFj2vlUnGhptfm3fmywQGn8Txhc_u.K MAZxof_P7e3baGPTuaVTIvJBv6y.K_TecgLGXA3_MUeN7eA0Et99EjbsFfQqjrzm8T7sIYekruUX TrRpXP3ldVzk36i4e_7hH6.66ImEbDIRu2EIZWGjOskTUZnG2JOjz4SVE7qIQoNjec0qYvWHSCph 9rUnScO_kA.mlVBCaB8hZA4D0wNa72kHFnFzywnuLM.8S0CPG3cNJDbpWmPOrjjx6Hiq4pAD354r jvNfO8fOkttzPFXQGqVfVr_9giARmnDknT2FX2pLH6A2e4RhJUQkl96ier3kUXk5IUNGjtM7HxKs j Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.ne1.yahoo.com with HTTP; Tue, 15 Sep 2020 22:33:28 +0000 Date: Tue, 15 Sep 2020 22:33:24 +0000 (UTC) From: Nagaraj Raj To: Pgsql Performance Message-ID: <1709308869.2763890.1600209204171@mail.yahoo.com> Subject: Single column vs composite partial index MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2763889_834070132.1600209204169" References: <1709308869.2763890.1600209204171.ref@mail.yahoo.com> X-Mailer: WebService/1.1.16583 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 Safari/537.36 Content-Length: 12267 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2763889_834070132.1600209204169 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi, I'm running one query, and I created two types of index one is composite an= d 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. explain analyze SELECT BAN, SUBSCRIBER_NO, ACTV_CODE, ACTV_RSN_CODE, EFFECT= IVE_DATE, TRX_SEQ_NO, LOAD_DTTM, rnk AS RNK=C2=A0 FROM ( SELECT CT.BAN, CT.= SUBSCRIBER_NO, CT.ACTV_CODE, CT.ACTV_RSN_CODE, CT.EFFECTIVE_DATE, CT.TRX_SE= Q_NO, CT.LOAD_DTTM, row_number() over (partition by CT.BAN, CT.SUBSCRIBER_N= O, 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 >=3D CURRENT_DATE - 7 ) S WHERE RN= K =3D 1 1st Index with single column:=20 CREATE INDEX l_csm_transactions_load_dttm_idx1=C2=A0 =C2=A0 ON sam_t.l_csm_= transactions USING btree=C2=A0 =C2=A0 (load_dttm ASC NULLS LAST) /*"Subquery Scan on s=C2=A0 (cost=3D32454.79..33555.15 rows=3D129 width=3D= 61) (actual time=3D56.473..56.473 rows=3D0 loops=3D1)""=C2=A0 Filter: (s.rn= k =3D 1)""=C2=A0 ->=C2=A0 WindowAgg=C2=A0 (cost=3D32454.79..33231.52 rows= =3D25891 width=3D61) (actual time=3D56.472..56.472 rows=3D0 loops=3D1)""=C2= =A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D32454.79..32519.51 row= s=3D25891 width=3D53) (actual time=3D56.470..56.470 rows=3D0 loops=3D1)""= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: ct.ban, ct.subsc= riber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.load_dttm = DESC""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: quickso= rt=C2=A0 Memory: 25kB""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->= =C2=A0 Bitmap Heap Scan on l_csm_transactions ct=C2=A0 (cost=3D1271.13..305= 56.96 rows=3D25891 width=3D53) (actual time=3D56.462..56.462 rows=3D0 loops= =3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Recheck Cond: ((actv_code)::text =3D ANY ('{NAC,CAN,RSP,RCL}'::text[]))= ""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Fil= ter: ((load_dttm)::date >=3D (CURRENT_DATE - 7))""=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Rows Removed by Filter: 79137= ""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Hea= p Blocks: exact=3D23976""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Bitmap Index Scan on l_csm_transactions_actv_= code_idx1=C2=A0 (cost=3D0.00..1264.66 rows=3D77673 width=3D0) (actual time= =3D6.002..6.002 rows=3D79137 loops=3D1)""Planning Time: 0.270 ms""Execution= Time: 56.639 ms"*/ 2nd one with composite and partial index: CREATE INDEX l_csm_transactions_actv_code_load_dttm_idx1=C2=A0 =C2=A0 ON sa= m_t.l_csm_transactions USING btree=C2=A0 =C2=A0 (actv_code COLLATE pg_catal= og."default" ASC NULLS LAST, (load_dttm::date) DESC NULLS FIRST)=C2=A0 =C2= =A0 WHERE actv_code::text =3D ANY (ARRAY['NAC'::character varying, 'CAN'::c= haracter varying, 'RSP'::character varying, 'RCL'::character varying]::text= []); /*"Subquery Scan on s=C2=A0 (cost=3D32023.15..33123.52 rows=3D129 width=3D6= 1) (actual time=3D2.256..2.256 rows=3D0 loops=3D1)""=C2=A0 Filter: (s.rnk = =3D 1)""=C2=A0 ->=C2=A0 WindowAgg=C2=A0 (cost=3D32023.15..32799.88 rows=3D2= 5891 width=3D61) (actual time=3D2.255..2.255 rows=3D0 loops=3D1)""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D32023.15..32087.88 rows=3D= 25891 width=3D53) (actual time=3D2.254..2.254 rows=3D0 loops=3D1)""=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: ct.ban, ct.subscriber_n= o, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.load_dttm DESC""= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: quicksort=C2= =A0 Memory: 25kB""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0= Bitmap Heap Scan on l_csm_transactions ct=C2=A0 (cost=3D1449.32..30125.32 = rows=3D25891 width=3D53) (actual time=3D2.247..2.247 rows=3D0 loops=3D1)""= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Reche= ck Cond: (((load_dttm)::date >=3D (CURRENT_DATE - 7)) AND ((actv_code)::tex= t =3D ANY ('{NAC,CAN,RSP,RCL}'::text[])))""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Bitmap Index Scan on l_csm_= transactions_actv_code_load_dttm_idx1=C2=A0 (cost=3D0.00..1442.85 rows=3D25= 891 width=3D0) (actual time=3D2.244..2.245 rows=3D0 loops=3D1)""=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 Index Cond: ((load_dttm)::date >=3D (CURRENT_DATE - 7))""Planning Ti= me: 0.438 ms""Execution Time: 2.303 ms"*/ Please suggest me the best choice. Appritiated the responce.=C2=A0 Thanks,Rj ------=_Part_2763889_834070132.1600209204169 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

I'm running one query, a= nd I created two types of index one is composite and the other one with sin= gle column one and query planner showing almost the same cost for both inde= x bitmap scan, I'm not sure which is appropriate to keep in production tabl= es.


explain analyze
=09= =09=09SELECT BAN, SUBSCRIBER_NO, ACTV_CODE, ACTV_RSN_CODE, EFFECTIVE= _DATE, TRX_SEQ_NO, LOAD_DTTM, rnk AS RNK  FROM (
=09=09=09SELECT CT.BAN, CT.SUBSCRIBER_NO= , CT.ACTV_CODE, CT.ACTV_RSN_CODE, CT.EFFECTIVE_DATE, CT.TRX_SEQ_NO, CT.LOAD= _DTTM,
=09=09=09=09=09row_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
<= div>=09=09=09FROM SAM_T.L_CSM= _TRANSACTIONS CT WHERE CT.ACTV_CODE in ( 'NAC', 'CAN', 'RSP', 'RCL') AND LO= AD_DTTM::DATE >=3D CURRENT_DATE - 7
=09=09=09) S WHERE RNK =3D 1
=09=09=09
=09=09=09
= 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)


=09/*"Subquery Scan on s  (cost=3D= 32454.79..33555.15 rows=3D129 width=3D61) (actual time=3D56.473..56.473 row= s=3D0 loops=3D1)"
"  Filter: (s.rnk =3D 1)"
" = ; ->  WindowAgg  (cost=3D32454.79..33231.52 rows=3D25891 width= =3D61) (actual time=3D56.472..56.472 rows=3D0 loops=3D1)"
" =       ->  Sort  (cost=3D32454.79..32519.51 row= s=3D25891 width=3D53) (actual time=3D56.470..56.470 rows=3D0 loops=3D1)"
"              Sort Key: ct.ban,= ct.subscriber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.l= oad_dttm DESC"
"              = Sort Method: quicksort  Memory: 25kB"
"      =         ->  Bitmap Heap Scan on l_csm_transacti= ons ct  (cost=3D1271.13..30556.96 rows=3D25891 width=3D53) (actual tim= e=3D56.462..56.462 rows=3D0 loops=3D1)"
"      &nb= sp;             Recheck Cond: ((actv_code)::t= ext =3D ANY ('{NAC,CAN,RSP,RCL}'::text[]))"
"     =               Filter: ((load_dttm)::dat= e >=3D (CURRENT_DATE - 7))"
"         = ;           Rows Removed by Filter: 79137"
"                    H= eap Blocks: exact=3D23976"
"          &n= bsp;         ->  Bitmap Index Scan on l_csm_tra= nsactions_actv_code_idx1  (cost=3D0.00..1264.66 rows=3D77673 width=3D0= ) (actual time=3D6.002..6.002 rows=3D79137 loops=3D1)"
"Planning = Time: 0.270 ms"
"Execution Time: 56.639 ms"*/

2nd one with composite and partial = index:

CREATE INDEX l_csm_transactions_actv_cod= e_load_dttm_idx1
    ON sam_t.l_csm_transactions USING = btree
    (actv_code COLLATE pg_catalog."default" ASC N= ULLS LAST, (load_dttm::date) DESC NULLS FIRST)
    WHER= E actv_code::text =3D ANY (ARRAY['NAC'::character varying, 'CAN'::character= varying, 'RSP'::character varying, 'RCL'::character varying]::text[]);


/*"Subquery Scan on s  (cost=3D3= 2023.15..33123.52 rows=3D129 width=3D61) (actual time=3D2.256..2.256 rows= =3D0 loops=3D1)"
"  Filter: (s.rnk =3D 1)"
" = ->  WindowAgg  (cost=3D32023.15..32799.88 rows=3D25891 width= =3D61) (actual time=3D2.255..2.255 rows=3D0 loops=3D1)"
"  &= nbsp;     ->  Sort  (cost=3D32023.15..32087.88 rows= =3D25891 width=3D53) (actual time=3D2.254..2.254 rows=3D0 loops=3D1)"
=
"              Sort Key: ct.ban, ct= .subscriber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.load= _dttm DESC"
"              Sor= t Method: quicksort  Memory: 25kB"
"      &nb= sp;       ->  Bitmap Heap Scan on l_csm_transactions= ct  (cost=3D1449.32..30125.32 rows=3D25891 width=3D53) (actual time= =3D2.247..2.247 rows=3D0 loops=3D1)"
"       =             Recheck Cond: (((load_dttm)::dat= e >=3D (CURRENT_DATE - 7)) AND ((actv_code)::text =3D ANY ('{NAC,CAN,RSP= ,RCL}'::text[])))"
"            &nb= sp;       ->  Bitmap Index Scan on l_csm_transaction= s_actv_code_load_dttm_idx1  (cost=3D0.00..1442.85 rows=3D25891 width= =3D0) (actual time=3D2.244..2.245 rows=3D0 loops=3D1)"
"  &n= bsp;                     =   Index Cond: ((load_dttm)::date >=3D (CURRENT_DATE - 7))"
"Planning Time: 0.438 ms"
"Execution Time: 2.303 ms"*/



= Please suggest me the best choice.

Appritiated the respon= ce. 


Thanks,
Rj

=
------=_Part_2763889_834070132.1600209204169--