Hello,
I am having performance issues with one of the query.
The query is taking 39 min to fetch 3.5 mil records.
I want to reduce that time to 15 mins.
could you please suggest something to its performance?
server configuration:
CPUs = 4
memory = 16 GM
shared_buffers = 3 GB
work_mem = 100MB
effective_cache_size = 12 GB
we are doing the vacuum/analyze regularly on the database.
attached is the query with its explain plan.
Thanks,
Samir Magar
query:
SELECT
DISTINCT
DLR_QLFY.DLR_QLFY_ID as DLR_QLFY_ID, NMQ_REQ.GRACE_PRD as GRACE_PRD, NMQ_REQ.HIDE_PRG_FLG as HIDE_PRG_FLG, NMQ_REQ.NTFY_DLR_FLG as NTFY_DLR_FLG, DLR_LOC.ACCT_NUM as ACCT_NUM, NMQ_REQ.NMQ_REQ_ID as NMQ_REQ_ID, NEW_MDL.PI_MDL_ID as PI_MDL_ID
FROM test.DLR_QLFY INNER JOIN
(SELECT DLR_GRP.DLR_GRP_ID AS LOC_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS LOC_ACCT_NUM FROM test.DLR_GRP, test.DLR_GRP_DLR_XREF, test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND (DLR_GRP.DLR_GRP_TYP='LOC' OR DLR_GRP.DLR_GRP_TYP='COG') AND DLR_LOC.IS_ACTV='Y' ) LOC_GRP
ON DLR_QLFY.QLFY_GRP_ID=LOC_GRP.LOC_GRP_ID
INNER JOIN (SELECT DLR_GRP.DLR_GRP_ID AS COG_GRP_ID,LEAD_DLR_LOC_ID,DLR_LOC.ACCT_NUM AS COG_ACCT_NUM FROM test.DLR_GRP,test.DLR_GRP_DLR_XREF,test.DLR_LOC WHERE DLR_GRP.DLR_GRP_ID=DLR_GRP_DLR_XREF.DLR_GRP_ID AND DLR_GRP_DLR_XREF.DLR_LOC_ID=DLR_LOC.DLR_LOC_ID AND DLR_GRP.DLR_GRP_TYP='COG' AND DLR_LOC.IS_ACTV='Y' ) COG_GRP
ON DLR_QLFY.COG_GRP_ID=COG_GRP.COG_GRP_ID
INNER JOIN test.DLR_LOC
ON DLR_LOC.ACCT_NUM=LOC_GRP.LOC_ACCT_NUM
AND DLR_LOC.ACCT_NUM=COG_GRP.COG_ACCT_NUM
INNER JOIN test.DLR_LOC LEAD_LOC
ON LEAD_LOC.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
AND LEAD_LOC.ACCT_NUM=LEAD_LOC.COG_PARNT_ACCT
INNER JOIN test.DLR_LOC COG_LEAD
ON COG_LEAD.DLR_LOC_ID=COG_GRP.LEAD_DLR_LOC_ID
INNER JOIN test.NMQ_REQ
ON DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID
INNER JOIN test.NEW_MDL
ON NMQ_REQ.NEW_MDL_ID = NEW_MDL.NEW_MDL_ID
INNER JOIN test.STG_ACFLX_NMQ_DLRS
ON COG_LEAD.ACCT_NUM=STG_ACFLX_NMQ_DLRS.RLTNP_LEAD_ACCT
AND STG_ACFLX_NMQ_DLRS.ACCT_ID=DLR_LOC.ACCT_NUM
WHERE
DLR_LOC.IS_ACTV='Y'
AND DLR_QLFY.QLF_FLG='N'
AND NMQ_REQ.PGM_DSBL_FLG != 'Y'
AND (NMQ_REQ.PGM_START_DT <= CURRENT_DATE
AND NMQ_REQ.PGM_END_DT > CURRENT_DATE)
AND DLR_QLFY.DLR_QLFY_ID NOT IN (SELECT DLR_QLFY.DLR_QLFY_ID FROM test.DLR_QLFY WHERE QLF_FLG='Y' AND DLR_QLFY.NMQ_REQ_ID=NMQ_REQ.NMQ_REQ_ID);
---------------------------------------------------------------------------
access plan
"HashAggregate (cost=4538.33..4538.34 rows=1 width=27)"
" Group Key: dlr_qlfy.dlr_qlfy_id, nmq_req.grace_prd, nmq_req.hide_prg_flg, nmq_req.ntfy_dlr_flg, dlr_loc.acct_num, nmq_req.nmq_req_id, new_mdl.pi_mdl_id"
" -> Nested Loop (cost=3.59..4538.31 rows=1 width=27)"
" -> Nested Loop (cost=3.31..4537.94 rows=1 width=27)"
" -> Nested Loop (cost=3.03..4530.16 rows=1 width=15)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1438.27 rows=263 width=15)"
" -> Nested Loop (cost=0.29..1306.78 rows=169 width=15)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.12 rows=169 width=4)"
" Filter: (acct_num = cog_parnt_acct)"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..3.24 rows=1 width=11)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.74 rows=1 width=33)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" -> Nested Loop (cost=2.17..11.41 rows=1 width=37)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.44 rows=1 width=8)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" -> Nested Loop (cost=1.58..10.63 rows=1 width=26)"
" -> Index Only Scan using idx_102 on dlr_loc (cost=0.29..0.32 rows=1 width=7)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4)"
" Index Cond: (dlr_grp_id = dlr_grp_dlr_xref.dlr_grp_id)"
" Filter: ((dlr_grp_typ = 'LOC'::bpchar) OR (dlr_grp_typ = 'COG'::bpchar))"
" -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 rows=1 width=16)"
" Index Cond: ((qlfy_grp_id = dlr_grp.dlr_grp_id) AND (qlf_flg = 'N'::bpchar) AND (cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id))"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.77 rows=1 width=16)"
" Index Cond: (nmq_req_id = dlr_qlfy.nmq_req_id)"
" Filter: ((pgm_dsbl_flg <> 'Y'::bpchar) AND (pgm_start_dt <= ('now'::cstring)::date) AND (pgm_end_dt > ('now'::cstring)::date) AND (NOT (SubPlan 1)))"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.81 rows=269 width=4)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
