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 1kIRQE-0006CS-SG for pgsql-performance@arkaria.postgresql.org; Wed, 16 Sep 2020 07:00:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kIRQD-0005fJ-68 for pgsql-performance@arkaria.postgresql.org; Wed, 16 Sep 2020 07:00:05 +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 1kIRQC-0005eE-Ea for pgsql-performance@lists.postgresql.org; Wed, 16 Sep 2020 07:00:04 +0000 Received: from sonic302-20.consmr.mail.ne1.yahoo.com ([66.163.186.146]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kIRQ5-0002R6-0q for pgsql-performance@lists.postgresql.org; Wed, 16 Sep 2020 07:00:03 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1600239594; bh=8MWHUdS5tWIkqWGfwwYT/4G6sU76T79qIHRf6odv9dE=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=YmqXWB4taVIJLY+CAScsCvuV8AT0heQmYNQlUoadXhbdNrhZGMAXDcfGi9JI+XNJP9pGqehl2F29NhgMyXfplZ8Q18IrWA/IPNYBnsiEEaTXfGrWRnDhokE4c1lYtY/2y5ZoydLYWtSRK1VCmAQ2liXZRqFmcOauYFRztGrttFLP6DMh15b50lWN9eY4hfO2vvWTmqzWV9h1LCqrrk7nuNzjAgOPflnxtJLDlV7l4wffre1WXIX/yWe6CGfH81dcnGc7lesjAAlssSu3r6LolQg6jhg+OuHiEV/ZNmAl/c+z6yEWWmjCPL9lbqRthGoCUMxZ4eAnKrS+FeW4r3gTig== X-YMail-OSG: 0HVzV10VM1mtq2GnrUArg5RrhU9Nz.Ek4wcF.Irv4_mdARP.p06DhQAgOkgtq0u XrVsQnDXiTwmiHKeyA4txLY9Zynh3DO3rDLmh9vMCoMOx6PgyS3Z3n4cDKoS6ws6mfhuMcTvwZEo 7bpIHw8xww7faWU2rucdqOhQxF7KQT0wamRlXn1mlc3VNi6vt9OvDg9F91tt9oI2gTNxJ0nmSOmg Xro551Xl4uH8Bj_lm5rNuKimh0knz6a4bj8YmJVrLpZxF7fJdjSy.YtV0nPL72UufIiVCnGY9XzK 1BZGhPDeTP02poUWbO2mZ8TRsY5egtI_UeRr2F7wmD8.ih48sqNsrmlDcC4pnCQo_ZjZsIqEk.Hq wI5_FuJmPZ9zjnMQU02JVQFYoThlnRkmgKHXfE7C_dtjwqaNND0M.TdTQjbDP0SSPPmpsfTcXLao KmQloRpADVDUgi_6EKiUQHu1GoimX_HlrSLV917giPbXOJpLOXdh1UEyqpuuj6B4NM2NRoIGrHnn YoOffUbG_ZpYTMxRMM7IECIeSgPQJgS9IP7dI1.wKqju_R3cV0N.JQA.06yMvYltBvihzFM6M0Hc x2iYy._b3tTdD2LtPND_LbsRVQ89nDUq2_Se6WsTsQTf1UxWUWvedAVjhjtDLH3FJAs._LFCFvyj zsmSNhw0lZS_CfmyzGvnUUAjs8a5P3jPTt35Kl5j6aY6guOEkeEdgkRAxc8OyVpI825qEsN4JoyG QFrKW8YTL_AWxNYGD8J6dZGiPLYGp0BAtUhZHPEe78p97pMJ4ST8d0uAJh1a42_A83qlSxpo7Yb. ljTsraFhdwdlQFJTC8jnHPNxWGpqovwJJISFhKiblu4jUnc.yYtEKrtovfJBQLk_bC8rMtoLXP2h gXxcwkUNex4.HVBno_tLCAZS1dK7mJkGCtZnNv1jS_ywuIF.IoallFzoThgHxi2eJ.LNgIPb2F3z 6gHYQscoG3iscbNs_CknX9anOMlM_NJ1gnMucPWZdVNXUe0g66c2sAx9Hs6CptMmAOXk9TK3lq.h Q.GEesI4tsJFnolXBEZCcYCMN1shKO8b0SJmVF9tQhBRcwgazjLjLrgP4mp8cvAEooWHkHRzuABb HV_Z5Y0jczMlVatsxBtU8apkMqE0tjbUzlN6XX_eEKucURwUg2xWnMnWvKvDwwZEAqWY_0qyk6lv A2O9cC8HsZ41AFGZidbPOrtCuBe8Ob8o7lMyB8nF2NvSC0r93m3pmAisouAxTa5C09qYv4fPCAvL .jKpaEWe2J_vurbxo6jfdI6cQ07Z8UHir63WTPd_VWWdwI9OUnKhnFm7pZlBiueU9e3crz4HFPUc gZwWc21YOGtRv306.e8OdeM0unXZ3vQB3F19vMsBx.csjNFWpY9raWFQe45Xmpm.xyfAjKp9o.s8 z6dePxlwkU5k8_gJXAObLZ6NJM1K8s747OFcA3TRbDVAicKi0MDJzyx9hx2G7TwacEUV2VgUSv7_ .YJ37ZiRFvQ29jofe4xkL1yZO7iJlbv9QO.DUktn_YgwDfA-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic302.consmr.mail.ne1.yahoo.com with HTTP; Wed, 16 Sep 2020 06:59:54 +0000 Date: Wed, 16 Sep 2020 06:59:51 +0000 (UTC) From: Nagaraj Raj To: Justin Pryzby Cc: Pgsql Performance Message-ID: <1474812918.2865518.1600239591273@mail.yahoo.com> In-Reply-To: <20200916041834.GW18552@telsasoft.com> References: <1709308869.2763890.1600209204171.ref@mail.yahoo.com> <1709308869.2763890.1600209204171@mail.yahoo.com> <20200916041834.GW18552@telsasoft.com> Subject: Re: Single column vs composite partial index MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2865517_12091158.1600239591269" 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: 22666 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2865517_12091158.1600239591269 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable >=C2=A0You'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 >=C2=A0It 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 bl= ock stored procedure, so it will be run more often on the table.=C2=A0 explain(ANALYZE, BUFFERS) output:=C2=A0 "Subquery Scan on s=C2=A0 (cost=3D32023.15..33123.52 rows=3D129 width=3D61)= (actual time=3D2.615..2.615 rows=3D0 loops=3D1)""=C2=A0 Filter: (s.rnk =3D= 1)""=C2=A0 Buffers: shared hit=3D218""=C2=A0 ->=C2=A0 WindowAgg=C2=A0 (cos= t=3D32023.15..32799.88 rows=3D25891 width=3D61) (actual time=3D2.614..2.615= rows=3D0 loops=3D1)""=C2=A0 =C2=A0 =C2=A0 =C2=A0 Buffers: shared hit=3D218= ""=C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D32023.15..32087.8= 8 rows=3D25891 width=3D53) (actual time=3D2.613..2.613 rows=3D0 loops=3D1)"= "=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: ct.ban, ct.subs= criber_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: quicks= ort=C2=A0 Memory: 25kB""=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Bu= ffers: shared hit=3D218""=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..30= 125.32 rows=3D25891 width=3D53) (actual time=3D2.605..2.605 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: (((load_dttm)::date >=3D (CURRENT_DATE - 7)) AND ((actv_c= ode)::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 Buffers: shared hit=3D218"= "=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=3D25891 width=3D0) (actual time=3D2.602..2.6= 02 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))""=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 Buffers: shared hit=3D218""Planni= ng 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=C2=A0 Thanks,Rj On Tuesday, September 15, 2020, 09:18:55 PM PDT, Justin Pryzby wrote: =20 =20 On Tue, Sep 15, 2020 at 10:33:24PM +0000, 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 t= he 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 index= es, and let the planner figure out which is best (it might bitmap-AND or -OR th= em 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.=C2=A0 Maybe you need to ANALYZE the table (= or adjust the autoanalyze thresholds), or evaluate if there's a correlation between columns.=C2=A0 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, EFFE= CTIVE_DATE, TRX_SEQ_NO, LOAD_DTTM, rnk AS RNK=C2=A0 FROM ( SELECT CT.BAN, C= T.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_DT= TM 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 = RNK =3D 1 > 1st Index with single column:=20 > CREATE INDEX l_csm_transactions_load_dttm_idx1=C2=A0 =C2=A0 ON sam_t.l_cs= m_transactions USING btree=C2=A0 =C2=A0 (load_dttm ASC NULLS LAST) >=C2=A0 /*"Subquery Scan on s=C2=A0 (cost=3D32454.79..33555.15 rows=3D129 w= idth=3D61) (actual time=3D56.473..56.473 rows=3D0 loops=3D1) >=C2=A0 =C2=A0 Filter: (s.rnk =3D 1) >=C2=A0 =C2=A0 ->=C2=A0 WindowAgg=C2=A0 (cost=3D32454.79..33231.52 rows=3D2= 5891 width=3D61) (actual time=3D56.472..56.472 rows=3D0 loops=3D1) >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Sort=C2=A0 (cost=3D32454.79..3= 2519.51 rows=3D25891 width=3D53) (actual time=3D56.470..56.470 rows=3D0 loo= ps=3D1) >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Key: ct.ban, = ct.subscriber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.lo= ad_dttm DESC >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: quick= sort=C2=A0 Memory: 25kB >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Bitmap He= ap Scan on l_csm_transactions ct=C2=A0 (cost=3D1271.13..30556.96 rows=3D258= 91 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 =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 =C2= =A0 Filter: ((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 =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 =C2= =A0 Heap 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 ->=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 ro= ws=3D79137 loops=3D1) >=C2=A0 Planning Time: 0.270 ms >=C2=A0 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 = sam_t.l_csm_transactions USING btree=C2=A0 =C2=A0 (actv_code COLLATE pg_cat= alog."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'= ::character varying, 'RSP'::character varying, 'RCL'::character varying]::t= ext[]); >=20 > /*"Subquery Scan on s=C2=A0 (cost=3D32023.15..33123.52 rows=3D129 width= =3D61) (actual time=3D2.256..2.256 rows=3D0 loops=3D1) >=C2=A0 =C2=A0 Filter: (s.rnk =3D 1) >=C2=A0 =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 ->=C2=A0 Sort=C2=A0 (cost=3D32023.15..3= 2087.88 rows=3D25891 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 =C2=A0 Sort Key: ct.ban, = ct.subscriber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.lo= ad_dttm DESC >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Sort Method: quick= sort=C2=A0 Memory: 25kB >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Bitmap He= ap Scan on l_csm_transactions ct=C2=A0 (cost=3D1449.32..30125.32 rows=3D258= 91 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 =C2= =A0 Recheck Cond: (((load_dttm)::date >=3D (CURRENT_DATE - 7)) AND ((actv_c= ode)::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 =C2= =A0 ->=C2=A0 Bitmap Index Scan on l_csm_transactions_actv_code_load_dttm_id= x1=C2=A0 (cost=3D0.00..1442.85 rows=3D25891 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 =C2=A0 Index Cond: ((load_dttm)::date >=3D (CURRENT_DATE = - 7)) >=C2=A0 Planning Time: 0.438 ms >=C2=A0 Execution Time: 2.303 ms"*/ =20 ------=_Part_2865517_12091158.1600239591269 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
> You're aski= ng whether to keep one index or the other?

My ask is which index can be used for the mention= ed query in production for better IO

>&n= bsp;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. 

<= /div>
explain(ANALYZE, BUFFERS)= output: 

"Subquer= y Scan on s  (cost=3D32023.15..33123.52 rows=3D129 width=3D61) (actual= time=3D2.615..2.615 rows=3D0 loops=3D1)"
"  Filter: (s.rnk = =3D 1)"
"  Buffers: shared hit=3D218"
"  ->= ;  WindowAgg  (cost=3D32023.15..32799.88 rows=3D25891 width=3D61)= (actual time=3D2.614..2.615 rows=3D0 loops=3D1)"
"    =     Buffers: shared hit=3D218"
"      &n= bsp; ->  Sort  (cost=3D32023.15..32087.88 rows=3D25891 width= =3D53) (actual time=3D2.613..2.613 rows=3D0 loops=3D1)"
"  &= nbsp;           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: quic= ksort  Memory: 25kB"
"          &nb= sp;   Buffers: shared hit=3D218"
"       = ;       ->  Bitmap Heap Scan on l_csm_transactions c= t  (cost=3D1449.32..30125.32 rows=3D25891 width=3D53) (actual time=3D2= .605..2.605 rows=3D0 loops=3D1)"
"        &nb= sp;           Recheck Cond: (((load_dttm)::date &g= t;=3D (CURRENT_DATE - 7)) AND ((actv_code)::text =3D ANY ('{NAC,CAN,RSP,RCL= }'::text[])))"
"              =       Buffers: shared hit=3D218"
"    &n= bsp;               ->  Bitmap In= dex Scan on l_csm_transactions_actv_code_load_dttm_idx1  (cost=3D0.00.= .1442.85 rows=3D25891 width=3D0) (actual time=3D2.602..2.602 rows=3D0 loops= =3D1)"
"                &= nbsp;         Index Cond: ((load_dttm)::date >=3D (C= URRENT_DATE - 7))"
"            &nb= sp;             Buffers: shared hit=3D218"
"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 a= re up to date

=
> And Postgres version.


<= span style=3D"font-family: Helvetica Neue, Helvetica, Arial, sans-serif;"><= br>
Thanks,
=
Rj

=20
=20
On Tuesday, September 15, 2020, 09:18:55 PM PDT, Justin= Pryzby <pryzby@telsasoft.com> wrote:


On Tue, Sep 15, 2020 at 10:33:24PM +0000, Nagaraj Raj = wrote:
> Hi,
> I'm running one qu= ery, and I created two types of index one is composite and the other one wi= th single column one and query planner showing almost the same cost for bot= h index bitmap scan, I'm not sure which is appropriate to keep in productio= n tables.

You're asking whether to kee= p one index or the other ?
It depends on *all* the querie= s you'll run, not just this one.
The most general thing t= o do would be to make multiple, single column indexes,
an= d 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 auto= analyze thresholds), or evaluate if there's a correlation between
columns.  Bad rowcount estimates beget bad plans and poor pe= rformance.

Also: you could use explain= (ANALYZE,BUFFERS).
I think the fast plan would be possibl= e with a tiny BRIN index on load_dttm.
(Possibly combined= indexes on actv_code or others).
If you also have a btre= e 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_RS= N_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_TRANSACTION= S CT WHERE CT.ACTV_CODE in ( 'NAC', 'CAN', 'RSP', 'RCL') AND LOAD_DTTM::DAT= E >=3D CURRENT_DATE - 7 ) S WHERE RNK =3D 1

> 1st Index with single column:
> CREATE= INDEX l_csm_transactions_load_dttm_idx1    ON sam_t.l_csm_transa= ctions USING btree    (load_dttm ASC NULLS LAST)

>  /*"Subquery Scan on s  (cost=3D32454.7= 9..33555.15 rows=3D129 width=3D61) (actual time=3D56.473..56.473 rows=3D0 l= oops=3D1)
>    Filter: (s.rnk =3D 1)
>    ->  WindowAgg  (cost=3D32454.79..= 33231.52 rows=3D25891 width=3D61) (actual time=3D56.472..56.472 rows=3D0 lo= ops=3D1)
>          ->&nbs= p; Sort  (cost=3D32454.79..32519.51 rows=3D25891 width=3D53) (actual t= ime=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.load_dttm DESC<= br clear=3D"none">>              &nbs= p; Sort Method: quicksort  Memory: 25kB
>  &= nbsp;             ->  Bitmap Heap Sca= n on l_csm_transactions ct  (cost=3D1271.13..30556.96 rows=3D25891 wid= th=3D53) (actual time=3D56.462..56.462 rows=3D0 loops=3D1)
>                   =   Recheck Cond: ((actv_code)::text =3D ANY ('{NAC,CAN,RSP,RCL}'::text= []))
>             =         Filter: ((load_dttm)::date >=3D (CURRENT_DA= TE - 7))
>            &n= bsp;         Rows Removed by Filter: 79137
>                  &n= bsp;   Heap Blocks: exact=3D23976
>    =                   ->  = Bitmap Index Scan on l_csm_transactions_actv_code_idx1  (cost=3D0.00..= 1264.66 rows=3D77673 width=3D0) (actual time=3D6.002..6.002 rows=3D79137 lo= ops=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    ON = sam_t.l_csm_transactions USING btree    (actv_code COLLATE pg_cat= alog."default" ASC NULLS LAST, (load_dttm::date) DESC NULLS FIRST)  &n= bsp; WHERE actv_code::text =3D ANY (ARRAY['NAC'::character varying, 'CAN'::= character varying, 'RSP'::character varying, 'RCL'::character varying]::tex= t[]);
>
> /*"Subquery Scan on s&= nbsp; (cost=3D32023.15..33123.52 rows=3D129 width=3D61) (actual time=3D2.25= 6..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)
>       =   ->  Sort  (cost=3D32023.15..32087.88 rows=3D25891 widt= h=3D53) (actual time=3D2.254..2.254 rows=3D0 loops=3D1)
&= gt;                Sort Key: ct.ban= , ct.subscriber_no, ct.actv_code, ct.actv_rsn_code, ct.trx_seq_no DESC, ct.= load_dttm DESC
>          &nb= sp;     Sort Method: quicksort  Memory: 25kB
>                ->  = Bitmap Heap Scan on l_csm_transactions ct  (cost=3D1449.32..30125.32 r= ows=3D25891 width=3D53) (actual time=3D2.247..2.247 rows=3D0 loops=3D1)
>                =       Recheck Cond: (((load_dttm)::date >=3D (CURRENT_DAT= E - 7)) AND ((actv_code)::text =3D ANY ('{NAC,CAN,RSP,RCL}'::text[])))
>                &= nbsp;     ->  Bitmap Index Scan on l_csm_transactions_act= v_code_load_dttm_idx1  (cost=3D0.00..1442.85 rows=3D25891 width=3D0) (= actual time=3D2.244..2.245 rows=3D0 loops=3D1)
> =                     &nbs= p;     Index Cond: ((load_dttm)::date >=3D (CURRENT_DATE - 7))=
>  Planning Time: 0.438 ms
>= ;  Execution Time: 2.303 ms"*/

------=_Part_2865517_12091158.1600239591269--