please find the EXPLAIN ANALYZE output.
On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <[email protected]>
wrote:
> Hi
>
> please send EXPLAIN ANALYZE output.
>
> Regards
>
> Pavel
>
> 2017-11-15 10:33 GMT+01:00 Samir Magar <[email protected]>:
>
>> 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 ([email protected]
>> )
>> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
