public inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Samir Magar <samirmagar8@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: query performance issue
Date: Wed, 15 Nov 2017 18:24:45 +0530
Message-ID: <CAA=to3gnNReZ62m3K3qbN_KL8Hnmrg4wfb4LefZYqUa9v4OOSQ@mail.gmail.com> (raw)
In-Reply-To: <CAFj8pRCyvCktcxtEQrmF4kw5oR1DNYd_4gJNd6mkjrHw_NPPPw@mail.gmail.com>
References: <CAA=to3gXpr94TqSAYXT9zwxYwY_xKrLFnu8ZJNgJKP-kWza2CA@mail.gmail.com>
<CAFj8pRCyvCktcxtEQrmF4kw5oR1DNYd_4gJNd6mkjrHw_NPPPw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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"
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: samirmagar8@gmail.com, pavel.stehule@gmail.com
Subject: Re: query performance issue
In-Reply-To: <CAA=to3gnNReZ62m3K3qbN_KL8Hnmrg4wfb4LefZYqUa9v4OOSQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox