please find the EXPLAIN ANALYZE output.
On Wed, Nov 15, 2017 at 3:13 PM, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote:
PavelRegardsHiplease send EXPLAIN ANALYZE output.2017-11-15 10:33 GMT+01:00 Samir Magar <samirmagar8@xxxxxxxxx>:--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 = 4memory = 16 GMshared_buffers = 3 GBwork_mem = 100MBeffective_cache_size = 12 GBwe 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
