In the below execution plan, the index scan on five_lima (table has 900m records) is where it's spending most of its time. I want to bring down the runtime to a few seconds, how do I optimize it? Tried forcing seq scan and ran vacuum/analyze but it is not helping.
As per explain analysis from depesz, the index scan on five_lima is spending 86% of time.
five_lima 2 43,600.875 ms 86.6 % Index Only Scan Backward 1 21,936.780 ms 50.3 % Index Scan 1 21,664.095 ms 49.7 %
https://explain.depesz.com/s/7lXg
GroupAggregate (cost=5236122.79..5238409.75 rows=19058 width=392) (actual time=50337.968..50338.284 rows=76 loops=1) Group Key: ((((((three.papa)::text || 'sierra_tango'::text) || (quebec_three.mike_india)::text) || bravo_five((quebec_three.sierra_uniform)::text, 3, 'november_golf'::text)) || 'lima_charlie'::text)), quebec_three.mike_india, quebec_three.sierra_uniform -> Sort (cost=5236122.79..5236170.44 rows=19058 width=120) (actual time=50337.880..50337.903 rows=773 loops=1) Sort Key: ((((((three.papa)::text || 'sierra_tango'::text) || (quebec_three.mike_india)::text) || bravo_five((quebec_three.sierra_uniform)::text, 3, 'november_golf'::text)) || 'lima_charlie'::text)), quebec_three.mike_india, quebec_three.sierra_uniform Sort Method: quicksort Memory: 142kB -> Hash Left Join (cost=5221327.29..5234767.95 rows=19058 width=120) (actual time=49423.721..50337.319 rows=773 loops=1) Hash Cond: (((quebec_three.mike_india)::bpchar = three.mike_india) AND (quebec_three.sierra_uniform = three.sierra_uniform)) -> GroupAggregate (cost=5221204.51..5233639.85 rows=19058 width=292) (actual time=49422.982..50336.121 rows=773 loops=1) Group Key: quebec_three.mike_india, quebec_three.sierra_uniform, quebec_three.whiskey, quebec_three.tango, quebec_three.juliet_charlie, quebec_three.victor_papa, quebec_three.yankee, quebec_three.india_papa, quebec_three.victor_charlie, quebec_three.november_hotel, quebec_three.hotel_november -> Sort (cost=5221204.51..5221680.96 rows=190580 width=228) (actual time=49408.728..49416.532 rows=250551 loops=1) Sort Key: quebec_three.mike_india, quebec_three.sierra_uniform, quebec_three.whiskey, quebec_three.tango, quebec_three.juliet_charlie, quebec_three.victor_papa, quebec_three.yankee, quebec_three.india_papa, quebec_three.victor_charlie, quebec_three.november_hotel, quebec_three.hotel_november Sort Method: quicksort Memory: 27472kB -> Subquery Scan on quebec_three (cost=5191626.46..5204490.61 rows=190580 width=228) (actual time=49045.224..49167.610 rows=250551 loops=1) -> Unique (cost=5191626.46..5198773.21 rows=190580 width=286) (actual time=49045.204..49136.969 rows=250551 loops=1) -> Sort (cost=5191626.46..5192102.91 rows=190580 width=286) (actual time=49045.190..49071.536 rows=252496 loops=1) Sort Key: mike_november1.sierra_uniform, mike_november1.charlie_six, mike_november1.foxtrot_india, (xray(mike_november1.delta_xray, 'zulu'::text)), mike_november1.whiskey, quebec_sierra.tango, quebec_sierra.juliet_charlie, golf.oscar_lima, (CASE WHEN ((("six_four"((five_hotel.tango)::text, 2))::integer = 5) AND (five_hotel.juliet_charlie <> november_november ('charlie_tango'::bpchar[]))) THEN 'oscar_romeo'::text ELSE NULL::text END), (CASE WHEN ((("six_four"((five_hotel.tango)::text, 2))::integer = 5) AND (five_hotel.juliet_charlie = ANY ('charlie_tango'::bpchar[]))) THEN 'romeo'::text ELSE NULL::text END), (CASE WHEN ((("six_four"((five_hotel.tango)::text, 2))::integer = 6) AND (five_hotel.juliet_charlie <> november_november ('charlie_tango'::bpchar[]))) THEN 'oscar_romeo'::text ELSE NULL::text END), (CASE WHEN ((("six_four"((five_hotel.tango)::text, 2))::integer = 6) AND (five_hotel.juliet_charlie = ANY ('charlie_tango'::bpchar[]))) THEN 'romeo'::text ELSE NULL::text END), (CASE WHEN (golf.oscar_lima five_romeo NOT NULL) THEN 'delta_foxtrot'::text ELSE 'oscar_romeo'::text END), (CASE WHEN (("six_four"((five_hotel.tango)::text, 2))::integer = 15) THEN 'oscar_romeo'::text ELSE NULL::text END) Sort Method: quicksort Memory: 41652kB -> Nested Loop Left Join (cost=661986.99..5174912.56 rows=190580 width=286) (actual time=1737.304..47625.922 rows=252496 loops=1) -> Gather (cost=661986.29..3041816.11 rows=190580 width=79) (actual time=1733.755..1827.448 rows=252383 loops=1) Workers Planned: 2 Workers Launched: 2 -> Parallel Hash Left Join (cost=660986.29..3021758.11 rows=79408 width=79) (actual time=1723.881..8094.375 rows=84128 loops=3) Hash Cond: (((mike_november1.mike_india)::text = (seven_quebec.mike_india)::text) AND ((mike_november1.foxtrot_india)::text = (seven_quebec.foxtrot_india)::text) AND (mike_november1.sierra_uniform = seven_quebec.sierra_uniform)) -> Nested Loop Left Join (cost=420698.30..2780844.78 rows=79408 width=74) (actual time=1263.213..7579.180 rows=84128 loops=3) -> Nested Loop Left Join (cost=420697.72..2359299.65 rows=79408 width=69) (actual time=1262.995..6114.930 rows=84128 loops=3) -> Nested Loop Left Join (cost=420697.15..1943062.02 rows=79408 width=53) (actual time=1262.617..4846.387 rows=83834 loops=3) -> Parallel Hash Left Join (cost=420691.40..1289489.61 rows=79408 width=53) (actual time=1262.432..3244.712 rows=83191 loops=3) Hash Cond: (((mike_november1.mike_india)::text = (five_hotel.mike_india)::text) AND ((mike_november1.foxtrot_india)::text = (five_hotel.foxtrot_india)::text) AND (mike_november1.sierra_uniform = five_hotel.sierra_uniform)) -> Parallel Index Scan using lima_papa on six_echo juliet_xray_xray (cost=0.57..867904.42 rows=79408 width=45) (actual time=1.304..1922.996 rows=83190 loops=3) Index Cond: ((delta_xray >= 'four_kilo'::timestamp without time zone) AND (delta_xray <= 'uniform'::timestamp without time zone) AND ((mike_india)::text = 'five_papa'::text)) Filter: (oscar_quebec = 'quebec_golf'::numeric) Rows Removed by Filter: 115955 -> Parallel Hash (cost=404875.54..404875.54 rows=421741 width=32) (actual time=1259.567..1259.567 rows=93164 loops=3) Buckets: 1048576 Batches: 1 Memory Usage: 27936kB -> Parallel Bitmap Heap Scan on delta_echo five_hotel (cost=87668.95..404875.54 rows=421741 width=32) (actual time=947.771..1217.127 rows=93164 loops=3) Recheck Cond: ((mike_india)::text = 'five_papa'::text) Heap Blocks: exact=24664 -> Bitmap Index Scan on india_three (cost=0.00..87415.90 rows=1012179 width=0) (actual time=935.805..935.805 rows=466562 loops=1) Index Cond: ((mike_india)::text = 'five_papa'::text) -> Bitmap Heap Scan on two_bravo juliet_xray_delta (cost=5.76..8.20 rows=1 width=32) (actual time=0.018..0.018 rows=1 loops=249572) Recheck Cond: (((charlie_six)::text = (mike_november1.charlie_six)::text) AND ((foxtrot_india)::text = (mike_november1.foxtrot_india)::text)) Filter: (((mike_india)::text = 'five_papa'::text) AND ((mike_india)::text = (mike_november1.mike_india)::text) AND (sierra_uniform = mike_november1.sierra_uniform)) Heap Blocks: exact=16 -> BitmapAnd (cost=5.76..5.76 rows=1 width=0) (actual time=0.016..0.016 rows=0 loops=249572) -> Bitmap Index Scan on victor_three (cost=0.00..2.74 rows=5 width=0) (actual time=0.010..0.010 rows=1 loops=249572) Index Cond: ((charlie_six)::text = (mike_november1.charlie_six)::text) -> Bitmap Index Scan on two_delta (cost=0.00..2.77 rows=16 width=0) (actual time=0.010..0.010 rows=1 loops=128367) Index Cond: ((foxtrot_india)::text = (mike_november1.foxtrot_india)::text) -> Index Scan using hotel_oscar on charlie_yankee golf (cost=0.57..5.21 rows=1 width=40) (actual time=0.015..0.015 rows=1 loops=251501) Index Cond: ((foxtrot_india)::text = (mike_november1.foxtrot_india)::text) Filter: (((mike_india)::text = 'five_papa'::text) AND ((mike_india)::text = (mike_november1.mike_india)::text) AND (sierra_uniform = mike_november1.sierra_uniform)) Rows Removed by Filter: 0 -> Index Scan using seven_victor on five_charlie bravo_oscar (cost=0.57..5.28 rows=1 width=23) (actual time=0.017..0.017 rows=1 loops=252383) Index Cond: ((charlie_six)::text = (mike_november1.charlie_six)::text) Filter: (((mike_india)::text = 'five_papa'::text) AND ((mike_india)::text = (mike_november1.mike_india)::text) AND (sierra_uniform = mike_november1.sierra_uniform)) Rows Removed by Filter: 0 -> Parallel Hash (cost=232855.56..232855.56 rows=198198 width=29) (actual time=459.697..459.697 rows=66317 loops=3) Buckets: 524288 Batches: 1 Memory Usage: 16608kB -> Parallel Bitmap Heap Scan on victor_four seven_quebec (cost=20722.12..232855.56 rows=198198 width=29) (actual time=111.386..434.496 rows=66317 loops=3) Recheck Cond: ((mike_india)::text = 'five_papa'::text) Heap Blocks: exact=27484 -> Bitmap Index Scan on four_charlie (cost=0.00..20603.20 rows=475676 width=0) (actual time=107.013..107.013 rows=227154 loops=1) Index Cond: ((mike_india)::text = 'five_papa'::text) -> Index Scan using hotel_whiskey on five_lima quebec_sierra (cost=0.70..11.08 rows=1 width=33) (actual time=0.179..0.180 rows=1 loops=252383) Index Cond: (((foxtrot_india)::text = (mike_november1.foxtrot_india)::text) AND ((mike_india)::text = (mike_november1.mike_india)::text) AND ((mike_india)::text = 'five_papa'::text) AND (sierra_uniform = mike_november1.sierra_uniform)) Filter: (bravo_lima = (delta_four 2)) Rows Removed by Filter: 6 SubPlan -> Result (cost=5.55..5.58 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1828065) InitPlan -> Limit (cost=0.70..5.55 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1828065) -> Index Only Scan Backward using hotel_whiskey on five_lima foxtrot_four (cost=0.70..5.55 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1828065) Index Cond: ((foxtrot_india = (quebec_sierra.foxtrot_india)::text) AND (mike_india = (quebec_sierra.mike_india)::text) AND (sierra_uniform = quebec_sierra.sierra_uniform) AND (oscar_quebec = quebec_sierra.oscar_quebec) AND (bravo_lima five_romeo NOT NULL)) Heap Fetches: 18062 -> Hash (cost=70.67..70.67 rows=1489 width=20) (actual time=0.644..0.644 rows=1489 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 95kB -> Seq Scan on bravo_zulu three (cost=0.00..70.67 rows=1489 width=20) (actual time=0.048..0.416 rows=1489 loops=1)
Planning time: 24.541 ms
Execution time: 50356.651 ms
Here is the query -
explain analyze select quebec_three.mike_india,quebec_three.sierra_uniform,papa ||'('||quebec_three.mike_india||bravo_five(quebec_three.sierra_uniform::text,3,'0')||')' as papa,
sum( dms_appl_pending + dms_appl_done + no_of_fee_pending + veri_appl_pending )no_of_appl_done,
sum(veri_appl_done)veri_appl_done,sum(veri_appl_rejected)veri_appl_rejected,
sum(veri_appl_pending)veri_appl_pending,sum(app_appl_done)appr_appl_done,sum(app_appl_rejected)appr_appl_rejected,
sum(app_appl_pending)appr_appl_pending
,sum(no_of_fee_pending)no_of_fee_pending,sum(no_of_fee_done)no_of_fee_done
,sum(dms_appl_pending)dms_appl_pending,sum(dms_appl_done)dms_appl_done
from(
select quebec_three.mike_india,quebec_three.sierra_uniform,
case when right(quebec_three.tango::text,2)::int=05 and quebec_three.juliet_charlie ='C' then count(distinct quebec_three.foxtrot_india) else 0 end veri_appl_done,
case when (victor_papa='R' ) then count(quebec_three.foxtrot_india) else 0 end as veri_appl_rejected,
case when (yankee='P' ) then count(distinct quebec_three.foxtrot_india) else 0 end as veri_appl_pending,
case when whiskey='A' and (right(quebec_three.tango::text,2)::int=06 and quebec_three.juliet_charlie ='C') then count(distinct quebec_three.foxtrot_india) else 0 end app_appl_done,
case when (india_papa='R') then count(distinct quebec_three.foxtrot_india) else 0 end as app_appl_rejected,
case when (victor_charlie='P') then count(distinct quebec_three.foxtrot_india) else 0 end as app_appl_pending,
case when november_hotel='P' then count(distinct quebec_three.foxtrot_india) else 0 end as no_of_fee_pending,
case when november_hotel='A' then count(distinct quebec_three.foxtrot_india) else 0 end as no_of_fee_done,
case when (hotel_november='P' ) then count(distinct quebec_three.foxtrot_india) else 0 end as dms_appl_pending,
case when right(quebec_three.tango::text,2)::int=15 and quebec_three.juliet_charlie ='C' then count(distinct quebec_three.foxtrot_india) else 0 end dms_appl_done
from(
select distinct quebec_three.mike_india,quebec_three.sierra_uniform ,quebec_three.charlie_six,quebec_three.foxtrot_india,xray(quebec_three.delta_xray,'dd-Mon-yyyy')delta_xray,quebec_three.whiskey,quebec_sierra.tango,quebec_sierra.juliet_charlie,golf.oscar_lima,
case when right(five_hotel.tango::text,2)::int=05 and five_hotel.juliet_charlie not in ('M','I') then oscar_romeo else null end yankee,
case when right(five_hotel.tango::text,2)::int=05 and five_hotel.juliet_charlie in ('M','I') then romeo else null end victor_papa,
case when right(five_hotel.tango::text,2)::int=06 and five_hotel.juliet_charlie not in ('M','I') then oscar_romeo else null end victor_charlie,
case when right(five_hotel.tango::text,2)::int=06 and five_hotel.juliet_charlie in ('M','I') then romeo else null end india_papa,
case when golf.oscar_lima is not null then delta_foxtrot else oscar_romeo end november_hotel,
case when right(five_hotel.tango::text,2)::int=15 then oscar_romeo else null end hotel_november
from six_echo quebec_three
left join delta_echo five_hotel on five_hotel.foxtrot_india=quebec_three.foxtrot_india and five_hotel.mike_india=quebec_three.mike_india and five_hotel.sierra_uniform=quebec_three.sierra_uniform
left join five_lima quebec_sierra on quebec_sierra.foxtrot_india=quebec_three.foxtrot_india and quebec_sierra.mike_india=quebec_three.mike_india and quebec_sierra.sierra_uniform=quebec_three.sierra_uniform
and quebec_sierra.bravo_lima =(select max(bravo_lima) from five_lima foxtrot_four where foxtrot_four.foxtrot_india=quebec_sierra.foxtrot_india
and foxtrot_four.mike_india=quebec_sierra.mike_india and foxtrot_four.sierra_uniform=quebec_sierra.sierra_uniform and foxtrot_four.oscar_quebec=quebec_sierra.oscar_quebec)
left join hsrp.vt_hsrp h on h.foxtrot_india=quebec_three.foxtrot_india and h.charlie_six=quebec_three.charlie_six and h.mike_india=quebec_three.mike_india and h.sierra_uniform=quebec_three.sierra_uniform
left join two_bravo juliet_xray_delta on juliet_xray_delta.foxtrot_india=quebec_three.foxtrot_india and juliet_xray_delta.charlie_six=quebec_three.charlie_six and juliet_xray_delta.mike_india=quebec_three.mike_india and juliet_xray_delta.sierra_uniform=quebec_three.sierra_uniform
left join charlie_yankee golf on golf.foxtrot_india=quebec_three.foxtrot_india and golf.mike_india=quebec_three.mike_india and golf.sierra_uniform=quebec_three.sierra_uniform
left join five_charlie bravo_oscar on bravo_oscar.charlie_six=quebec_three.charlie_six and bravo_oscar.mike_india=quebec_three.mike_india and bravo_oscar.sierra_uniform=quebec_three.sierra_uniform
left join victor_four seven_quebec on seven_quebec.foxtrot_india=quebec_three.foxtrot_india and seven_quebec.mike_india=quebec_three.mike_india and seven_quebec.sierra_uniform=quebec_three.sierra_uniform
left join vm_vh_class vh on vh.vh_class=COALESCE(bravo_oscar.vh_class,seven_quebec.vh_class)
where quebec_three.mike_india='UP' and case when 0=0 then true else quebec_three.sierra_uniform=0 end and quebec_three.delta_xray between '2021-03-01 00:00:00.000000 +05:30' and ('2021-04-02 23:59:59.999000 +05:30'::date + interval '1 day' - interval '1 sec')
and quebec_three.oscar_quebec in (123)
)quebec_three
group by 1,2,whiskey,quebec_three.tango,quebec_three.juliet_charlie,victor_papa,yankee,india_papa,victor_charlie,november_hotel,hotel_november
)quebec_three
left join bravo_zulu three on three.mike_india=quebec_three.mike_india and three.sierra_uniform=quebec_three.sierra_uniform
group by 1,2,3 order by 3;
Adding orignal partial query/indexes/plan -
Partial query:
.....
vow4(# left join vha_status c on c.appl_no=a.appl_no and c.state_cd=a.state_cd and c.off_cd=a.off_cd
vow4(# and c.moved_on =(select max(moved_on) from vha_status c1 where c1.appl_no=c.appl_no
vow4(# and c1.state_cd=c.state_cd and c1.off_cd=c.off_cd and c1.pur_cd=c.pur_cd)
.....
vow4(# where a.state_cd='UP' and case when 0=0 then true else a.off_cd=0 end and a.appl_dt between '2021-03-01 00:00:00.000000 +05:30' and ('2021-04-02 23:59:59.999000 +05:30'::date + interval '1 day' - interval '1 sec')
vow4(# and a.pur_cd in (123)
Indexes: "vha_status_pkey" PRIMARY KEY, btree (appl_no, pur_cd, file_movement_slno) "idx_state_cd_vha_status" btree (state_cd) "va_status_moved_on_indx" btree (moved_on) "vha_status_appl_no_state_cd_off_cd_pur_cd_moved_on_idx" btree (appl_no, state_cd, off_cd, pur_cd, moved_on) "vha_status_movedon_state_cd_off_cd_idx" btree (moved_on, state_cd, off_cd)
Partial Plan:
-> Index Scan using vha_status_appl_no_state_cd_off_cd_pur_cd_moved_on_idx on vha_status c (cost=0.70..11.08 rows=1 width=33) (actual time=0.179..0.180 rows=1 loops=252383) Index Cond: (((appl_no)::text = (a_1.appl_no)::text) AND ((state_cd)::text = (a_1.state_cd)::text) AND ((state_cd)::text = 'UP'::text) AND (off_cd = a_1.off_cd)) Filter: (moved_on = (SubPlan 2)) Rows Removed by Filter: 6 SubPlan 2 -> Result (cost=5.55..5.58 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=1828065) InitPlan 1 (returns $4) -> Limit (cost=0.70..5.55 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1828065) -> Index Only Scan Backward using vha_status_appl_no_state_cd_off_cd_pur_cd_moved_on_idx on vha_status c1 (cost=0.70..5.55 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1828065) Index Cond: ((appl_no = (c.appl_no)::text) AND (state_cd = (c.state_cd)::text) AND (off_cd = c.off_cd) AND (pur_cd = c.pur_cd) AND (moved_on IS NOT NULL)) Heap Fetches: 18062