pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Single column vs composite partial index
Date: Tue, 15 Sep 2020 22:33:24 +0000 (UTC)
Message-ID: <1709308869.2763890.1600209204171@mail.yahoo.com> (raw )
References: <1709308869.2763890.1600209204171.ref@mail.yahoo.com >
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.
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,RCL}'::text[]))"" Filter: ((load_dttm)::date >= (CURRENT_DATE - 7))"" Rows Removed by Filter: 79137"" Heap Blocks: exact=23976"" -> Bitmap Index Scan on l_csm_transactions_actv_code_idx1 (cost=0.00..1264.66 rows=77673 width=0) (actual time=6.002..6.002 rows=79137 loops=1)""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_catalog."default" ASC NULLS LAST, (load_dttm::date) DESC NULLS FIRST) WHERE actv_code::text = ANY (ARRAY['NAC'::character varying, 'CAN'::character varying, 'RSP'::character varying, 'RCL'::character varying]::text[]);
/*"Subquery Scan on s (cost=32023.15..33123.52 rows=129 width=61) (actual time=2.256..2.256 rows=0 loops=1)"" Filter: (s.rnk = 1)"" -> WindowAgg (cost=32023.15..32799.88 rows=25891 width=61) (actual time=2.255..2.255 rows=0 loops=1)"" -> Sort (cost=32023.15..32087.88 rows=25891 width=53) (actual time=2.254..2.254 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=1449.32..30125.32 rows=25891 width=53) (actual time=2.247..2.247 rows=0 loops=1)"" Recheck Cond: (((load_dttm)::date >= (CURRENT_DATE - 7)) AND ((actv_code)::text = ANY ('{NAC,CAN,RSP,RCL}'::text[])))"" -> Bitmap Index Scan on l_csm_transactions_actv_code_load_dttm_idx1 (cost=0.00..1442.85 rows=25891 width=0) (actual time=2.244..2.245 rows=0 loops=1)"" Index Cond: ((load_dttm)::date >= (CURRENT_DATE - 7))""Planning Time: 0.438 ms""Execution Time: 2.303 ms"*/
Please suggest me the best choice.
Appritiated the responce.
Thanks,Rj
view thread (4+ messages) latest in thread
Message-ID: <1709308869.2763890.1600209204171@mail.yahoo.com>
Permalink: ../1709308869.2763890.1600209204171@mail.yahoo.com/
Also on: postgresql.org/message-id/1709308869.2763890.1600209204171@mail.yahoo.com
copy link · copy postgr.es
reply 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: nagaraj.sf@yahoo.com, pgsql-performance@lists.postgresql.org
Subject: Re: Single column vs composite partial index
In-Reply-To: <1709308869.2763890.1600209204171@mail.yahoo.com>
* 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