public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedquery performance issue
7+ messages / 4 participants
[nested] [flat]
* query performance issue
@ 2017-11-15 09:33 Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 14:16 ` Re: query performance issue Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 2 replies; 7+ messages in thread
From: Samir Magar @ 2017-11-15 09:33 UTC (permalink / raw)
To: pgsql-performance
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
[text/plain] query with access plan.txt (6.9K, 3-query%20with%20access%20plan.txt)
download | inline:
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)"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
@ 2017-11-15 09:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Re: query performance issue Samir Magar <samirmagar8@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2017-11-15 09:43 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance
Hi
please send EXPLAIN ANALYZE output.
Regards
Pavel
2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
> 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
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
@ 2017-11-15 12:54 ` Samir Magar <samirmagar8@gmail.com>
2017-11-15 13:12 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Samir Magar @ 2017-11-15 12:54 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-performance
please find the EXPLAIN ANALYZE output.
On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:
> Hi
>
> please send EXPLAIN ANALYZE output.
>
> Regards
>
> Pavel
>
> 2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
>
>> 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
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance
>>
>>
>
"HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
" 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..4459.67 rows=1 width=27) (actual time=0.228..2864594.177 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.31..4459.29 rows=1 width=27) (actual time=0.221..2819927.249 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.03..4451.45 rows=1 width=15) (actual time=0.158..36816.304 rows=12612983 loops=1)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1358.94 rows=263 width=15) (actual time=0.046..363.150 rows=52261 loops=1)"
" -> Nested Loop (cost=0.29..1227.46 rows=169 width=15) (actual time=0.024..86.909 rows=12151 loops=1)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.80 rows=169 width=4) (actual time=0.010..31.028 rows=12151 loops=1)"
" Filter: (acct_num = cog_parnt_acct)"
" Rows Removed by Filter: 21593"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..2.77 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=12151)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" Heap Fetches: 0"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14) (actual time=0.008..0.019 rows=4 loops=12151)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.75 rows=1 width=33) (actual time=0.058..0.615 rows=241 loops=52261)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=52261)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=2.17..11.42 rows=1 width=37) (actual time=0.051..0.530 rows=236 loops=53436)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11) (actual time=0.015..0.016 rows=1 loops=53436)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=53436)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=53402)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.58..10.64 rows=1 width=26) (actual time=0.036..0.425 rows=243 loops=51988)"
" -> Index Only Scan using idx10 on dlr_loc (cost=0.29..0.32 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=51988)"
" Index Cond: ((is_actv = 'Y'::bpchar) AND (acct_num = stg_acflx_nmq_dlrs.acct_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19) (actual time=0.026..0.354 rows=243 loops=51988)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11) (actual time=0.006..0.006 rows=1 loops=51988)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16) (actual time=0.019..0.273 rows=243 loops=51988)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=51988)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" Heap Fetches: 0"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20) (actual time=0.015..0.105 rows=121 loops=103987)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=103987)"
" 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) (actual time=0.009..0.066 rows=121 loops=103987)"
" 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))"
" Heap Fetches: 0"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.83 rows=1 width=16) (actual time=0.219..0.220 rows=1 loops=12612983)"
" 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)))"
" Rows Removed by Filter: 0"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.91 rows=274 width=4) (actual time=0.008..0.153 rows=576 loops=12321289)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12321289)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
"Planning time: 69.774 ms"
"Execution time: 2892445.829 ms"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Attachments:
[text/plain] explain with analyze.txt (6.9K, 3-explain%20with%20analyze.txt)
download | inline:
"HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
" 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..4459.67 rows=1 width=27) (actual time=0.228..2864594.177 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.31..4459.29 rows=1 width=27) (actual time=0.221..2819927.249 rows=12321289 loops=1)"
" -> Nested Loop (cost=3.03..4451.45 rows=1 width=15) (actual time=0.158..36816.304 rows=12612983 loops=1)"
" Join Filter: (lead_loc.dlr_loc_id = dlr_grp_1.lead_dlr_loc_id)"
" -> Nested Loop (cost=0.58..1358.94 rows=263 width=15) (actual time=0.046..363.150 rows=52261 loops=1)"
" -> Nested Loop (cost=0.29..1227.46 rows=169 width=15) (actual time=0.024..86.909 rows=12151 loops=1)"
" -> Seq Scan on dlr_loc lead_loc (cost=0.00..757.80 rows=169 width=4) (actual time=0.010..31.028 rows=12151 loops=1)"
" Filter: (acct_num = cog_parnt_acct)"
" Rows Removed by Filter: 21593"
" -> Index Only Scan using "IDX_101" on dlr_loc cog_lead (cost=0.29..2.77 rows=1 width=11) (actual time=0.003..0.004 rows=1 loops=12151)"
" Index Cond: (dlr_loc_id = lead_loc.dlr_loc_id)"
" Heap Fetches: 0"
" -> Index Scan using idx_14 on stg_acflx_nmq_dlrs (cost=0.29..0.63 rows=15 width=14) (actual time=0.008..0.019 rows=4 loops=12151)"
" Index Cond: (rltnp_lead_acct = cog_lead.acct_num)"
" -> Nested Loop (cost=2.45..11.75 rows=1 width=33) (actual time=0.058..0.615 rows=241 loops=52261)"
" -> Index Only Scan using idx3 on dlr_grp dlr_grp_1 (cost=0.29..0.32 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=52261)"
" Index Cond: ((lead_dlr_loc_id = cog_lead.dlr_loc_id) AND (dlr_grp_typ = 'COG'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=2.17..11.42 rows=1 width=37) (actual time=0.051..0.530 rows=236 loops=53436)"
" Join Filter: (dlr_loc_2.acct_num = dlr_loc.acct_num)"
" -> Nested Loop (cost=0.58..0.77 rows=1 width=11) (actual time=0.015..0.016 rows=1 loops=53436)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_2 (cost=0.29..0.32 rows=1 width=11) (actual time=0.009..0.009 rows=1 loops=53436)"
" Index Cond: ((acct_num = stg_acflx_nmq_dlrs.acct_id) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref dlr_grp_dlr_xref_1 (cost=0.29..0.43 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=53402)"
" Index Cond: ((dlr_loc_id = dlr_loc_2.dlr_loc_id) AND (dlr_grp_id = dlr_grp_1.dlr_grp_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.58..10.64 rows=1 width=26) (actual time=0.036..0.425 rows=243 loops=51988)"
" -> Index Only Scan using idx10 on dlr_loc (cost=0.29..0.32 rows=1 width=7) (actual time=0.009..0.009 rows=1 loops=51988)"
" Index Cond: ((is_actv = 'Y'::bpchar) AND (acct_num = stg_acflx_nmq_dlrs.acct_id))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.29..10.30 rows=1 width=19) (actual time=0.026..0.354 rows=243 loops=51988)"
" -> Index Only Scan using idx6 on dlr_loc dlr_loc_1 (cost=0.29..0.34 rows=1 width=11) (actual time=0.006..0.006 rows=1 loops=51988)"
" Index Cond: ((acct_num = dlr_loc.acct_num) AND (is_actv = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Nested Loop (cost=1.00..9.95 rows=1 width=16) (actual time=0.019..0.273 rows=243 loops=51988)"
" -> Index Only Scan using idx7 on dlr_grp_dlr_xref (cost=0.29..0.35 rows=2 width=8) (actual time=0.002..0.003 rows=2 loops=51988)"
" Index Cond: (dlr_loc_id = dlr_loc_1.dlr_loc_id)"
" Heap Fetches: 0"
" -> Nested Loop (cost=0.71..4.79 rows=1 width=20) (actual time=0.015..0.105 rows=121 loops=103987)"
" -> Index Scan using idxdg3 on dlr_grp (cost=0.29..0.33 rows=1 width=4) (actual time=0.005..0.006 rows=1 loops=103987)"
" 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) (actual time=0.009..0.066 rows=121 loops=103987)"
" 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))"
" Heap Fetches: 0"
" -> Index Scan using p_key_29 on nmq_req (cost=0.28..7.83 rows=1 width=16) (actual time=0.219..0.220 rows=1 loops=12612983)"
" 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)))"
" Rows Removed by Filter: 0"
" SubPlan 1"
" -> Index Only Scan using idx11 on dlr_qlfy dlr_qlfy_1 (cost=0.43..13.91 rows=274 width=4) (actual time=0.008..0.153 rows=576 loops=12321289)"
" Index Cond: ((nmq_req_id = nmq_req.nmq_req_id) AND (qlf_flg = 'Y'::bpchar))"
" Heap Fetches: 0"
" -> Index Scan using idx1 on new_mdl (cost=0.28..0.37 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=12321289)"
" Index Cond: (new_mdl_id = nmq_req.new_mdl_id)"
"Planning time: 69.774 ms"
"Execution time: 2892445.829 ms"
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Re: query performance issue Samir Magar <samirmagar8@gmail.com>
@ 2017-11-15 13:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 19:58 ` Re: query performance issue Gunther <raj@gusw.net>
0 siblings, 1 reply; 7+ messages in thread
From: Pavel Stehule @ 2017-11-15 13:12 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance
2017-11-15 13:54 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
> please find the EXPLAIN ANALYZE output.
>
> On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@gmail.com>
> wrote:
>
>> Hi
>>
>> please send EXPLAIN ANALYZE output.
>>
>> Regards
>>
>> Pavel
>>
>> 2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@gmail.com>:
>>
>>> 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.
>>>
>>>
There is wrong plan due wrong estimation
for this query you should to penalize nested loop
set enable_nestloop to off;
before evaluation of this query
Thanks,
>>> Samir Magar
>>>
>>>
>>> --
>>> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.
>>> org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-performance
>>>
>>>
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Re: query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 13:12 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
@ 2017-11-15 19:58 ` Gunther <raj@gusw.net>
2017-11-15 20:07 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Gunther @ 2017-11-15 19:58 UTC (permalink / raw)
To: pgsql-performance
On 11/15/2017 8:12, Pavel Stehule wrote:
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
You are not the only one with this issue. May I suggest to look at this
thread a little earlier this month.
http://www.postgresql-archive.org/OLAP-reporting-queries-fall-into-nested-loops-over-seq-scans-or-ot...
where this has been discussed in some length.
regards,
-Gunther
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Re: query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 13:12 ` Re: query performance issue Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 19:58 ` Re: query performance issue Gunther <raj@gusw.net>
@ 2017-11-15 20:07 ` Pavel Stehule <pavel.stehule@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Pavel Stehule @ 2017-11-15 20:07 UTC (permalink / raw)
To: Gunther <raj@gusw.net>; +Cc: pgsql-performance
2017-11-15 20:58 GMT+01:00 Gunther <raj@gusw.net>:
>
> On 11/15/2017 8:12, Pavel Stehule wrote:
>
> There is wrong plan due wrong estimation
>
> for this query you should to penalize nested loop
>
> set enable_nestloop to off;
>
> before evaluation of this query
>
>
> You are not the only one with this issue. May I suggest to look at this
> thread a little earlier this month.
>
> http://www.postgresql-archive.org/OLAP-reporting-queries-
> fall-into-nested-loops-over-seq-scans-or-other-horrible-
> planner-choices-tp5990160.html
>
> where this has been discussed in some length.
>
It is typical issue. The source of these problems are correlations between
columns (it can be fixed partially by multicolumn statistics in PostgreSQL
10). Another problem is missing multi table statistics - PostgreSQL planner
expects so any value from dictionary has same probability, what is not
usually true. Some OLAP techniques like calendar tables has usually very
bad impact on estimations with this results.
Regards
Pavel
> regards,
> -Gunther
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: query performance issue
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
@ 2017-11-15 14:16 ` Justin Pryzby <pryzby@telsasoft.com>
1 sibling, 0 replies; 7+ messages in thread
From: Justin Pryzby @ 2017-11-15 14:16 UTC (permalink / raw)
To: Samir Magar <samirmagar8@gmail.com>; +Cc: pgsql-performance; Pavel Stehule <pavel.stehule@gmail.com>
On Wed, Nov 15, 2017 at 03:03:39PM +0530, Samir Magar wrote:
> 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?
> "HashAggregate (cost=4459.68..4459.69 rows=1 width=27) (actual time=2890035.403..2892173.601 rows=3489861 loops=1)"
Looks to me like the problem is here:
> " -> Index Only Scan using idxdq7 on dlr_qlfy (cost=0.43..4.45 ROWS=1 width=16) (actual time=0.009..0.066 ROWS=121 loops=103987)"
> " 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))"
> " Heap Fetches: 0"
Returning 100x more rows than expected and bubbling up through a cascade of
nested loops.
Are those 3 conditions independent ? Or, perhaps, are rows for which
"qlfy_grp_id=dlr_grp.dlr_grp_id" is true always going to have
"cog_grp_id = dlr_grp_dlr_xref_1.dlr_grp_id" ?
Even if it's not "always" true, if rows which pass the one condition are more
likely to pass the other condition, this will cause an underestimate, as
obvserved.
You can do an experiment SELECTing just from those two tables joined and see if
you can reproduce the problem with poor rowcount estimate (hopefully in much
less than 15min).
If you can't drop one of the two conditions, you can make PG treat it as a
single condition for purpose of determining expected selectivity, using a ROW()
comparison like:
ROW(qlfy_grp_id, cog_grp_id) = ROW(dlr_grp.dlr_grp_id, dlr_grp_dlr_xref_1.dlr_grp_id)
If you're running PG96+ you may also be able to work around this by adding FKs.
Justin
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-11-15 20:07 UTC | newest]
Thread overview: 7+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-11-15 09:33 query performance issue Samir Magar <samirmagar8@gmail.com>
2017-11-15 09:43 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 12:54 ` Samir Magar <samirmagar8@gmail.com>
2017-11-15 13:12 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 19:58 ` Gunther <raj@gusw.net>
2017-11-15 20:07 ` Pavel Stehule <pavel.stehule@gmail.com>
2017-11-15 14:16 ` Justin Pryzby <pryzby@telsasoft.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox